Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.