Calculated measure for avg sales over last 3 years

Hello,

I am new at MDX and I want to create a calculated measure which gets the avg sales qty over the last 3 years. The average has to be calculated as of today's date (so with the Now() statement the system date is being fetched).

I have a facttable with all the sales quantities and i have a time table which contains the levels year, quarter, month and day.

Does anyone know how to do this? Thanx in advance.

Greetings,

Patrick

Patrick,

IF your last member in date dimension does not represent last loaded day, then you will have to use NOW() to get Current date. Example of such query depends on your date format. You can use date day key or name to generate member using format function.

Lets say your Day Key has format YYYYMMDD, then this will give you current day member:

StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")

So average 3 last years (AVG([Date 3 year ago]: [Todays Date], Measure)

CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS

Avg(

{ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Sales Qty]

) ;

Vidas Matelis

|||

Hi Vidal,

Thanx for the answer. I can't get it to work though. The following part gives a Token not valid error:

StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")

Another thing is. In my date hierarchy I have the last day. So if I wanted to get it from there how would it work then?

Greetings,

Patrick

|||

Patrick,

This is code is just an example. You would have to substitute your date hierarchy name and your date format.

Is your date key in the format YYYYMMDD? If no, what format is it? And what format is your date name? Also what is your date dimension hierarchy levels?

Vidas Matelis

|||

Hello Vidas,

Ik changed the hierarchy name like under:

Avg(

{ParallelPeriod([Period].[Year], 3, StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]"))

: StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Qty sales abs.])

I get the follwing error when trying to parse this:

Formula error - Syntax error - token is not valid: "{ParallelPeriod([Period].[Year], 3, StrToMember([Period].[Year].&[ + Format(now(), "yyyyMMdd") + "]^"^))

: StrToMember([Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]")}

, [Measures].[Qty sales abs.])

Best regards,

Patrick

|||

Patrick,

Could you please try first this simplified query:

SELECT {[Measures].[YourMeasureName]} ON 0
, {StrToMember("[Period].[Year].&[" + Format(now(), "yyyyMMdd") + "]") } ON 1
FROM [YourCubeName];

This query should return todays values. If it is not, you can focus on fixing this query instead of more complicated one.

Most importantly here: do you date members with key defined in the format YYYYMMDD, example: 20070507? If no, you would have to adjust format function to match your format. Examples for that:

Format(now(), "MMMM dd, yyyy") : May 07, 2007

Format(now(), "MMMM d, yyyy") : May 7, 2007

...

Some other formating options:

M - one or 2 digits. Example: 1, 11
MM - Month 2 digit, Example: 01, 11
MMM - Month 3 characters, Example: Jan, Nov
d - day one or 2 digits, Example: 1, 11
yy - year last 2 digits, Example: 07
yyyy - year 4 digits, Example: 2007
....

|||

Hi Vidas,

I managed to get it working now. The wrong formatting caused my problems. Furthermore I used a combination of avg and LastPeriods to get to the result, but you pushed me in the right direction.

Thanx for the help.

Greetings,

Patrick