Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jordi75
Starting Member
4 Posts |
 Posted - 2011-10-25 : 03:59:21
|
| HII have to do a report with a calendar hierarchy on column. I want a calculated measure or a scope statement that give me the average of month at year level.Hi have tried this:AVG( [Calendar].[year-month-day].currentmember.children,[Measures].[Value])but when i filter some month the calculation didn't changeFor example if I filter for jan 2011 and feb 2011, i still get the average of all 2011 months What is wrong?Thanks |
|
|
ssdeveloper
Starting Member
37 Posts |
Posted - 2011-10-25 : 14:16:09
|
| Have you tried to just work with the currentmember but not the .childrenas in:AVG( [Calendar].[year-month-day].currentmember,[Measures].[Value]) |
 |
|
|
jordi75
Starting Member
4 Posts |
Posted - 2011-10-26 : 04:06:04
|
| Without .children I get the sum of month, because I get current year value/number of year, that is year_value/1 |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-10-26 : 04:37:24
|
| Whats your join between your calendar and your measure in the dsv?Do you have a datekey or are you simply joining on a datetime? |
|
|
|
jordi75
Starting Member
4 Posts |
Posted - 2011-10-26 : 04:56:09
|
| I have a primary key datetime field with values like this :"2011-01-01 00:00:00.000". I have generated the time table in BIDS |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-10-26 : 11:23:44
|
| So the primary key in the calendar is datetime. What about the fact table? Are those values for the join without times as well?If not, then you may be better off creating a surrogate key for the join. |
|
|
|
jordi75
Starting Member
4 Posts |
Posted - 2011-10-26 : 11:53:22
|
| in Fact table I have the same data type: datetime "2011-01-01 00:00:00.000" |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-10-26 : 12:39:23
|
| If that is the case and you have a year-month-day heirarchy, then your query should work. What is it giving you in BIDS?The other way to get an average is to have your calculated member be simply ([Measures].[Value] / [Measures].[Count]), this should work on ever level of your heirarchy.. |
|
|
|
|
|
|
Average of selected children