MDX for Rolling Total

[Flybridge]

Hi

I am not trained in MDX and I struggle to get my head around the syntax.

I also posted this on the general OLAP forum, before I remembered this one!

We have a reporting need, out of a cube in MS Analysis Manager, to show the months Jan to Dec as columns. For the measure concerned we need to show the monthly value (no problem) and also the rolling 12 month total up to that month. i.e. for Jan this year the total from Feb 04 to Jan 05, for Feb this year the total of March 04 to Feb 05 etc across the page.

I have found a statement for a moving average which is close, but it assumes you start at the beginning of each year i.e. it doesn't include the last periods of the preceding year when working with the early periods of the current year, so Jan just has Jan's figure, Feb is the average of Jan and Feb, when it should be including Nov &/or Dec of the previous year. I need to cross the year end and go back to a relative period in the preceding year.

I suspect it needs the use of the parallelperiod function, but I can't work it out.

I suspect some intelligent being out there has already done this. Can anyone help?

Thanks in advance,

Steve


40 million lemmings can't be wrong........can they?

 

[Flybridge]

Well, it's been a while since I posted this, without response. But just in case anyone should feel the urge to answer this, I have now figured it out.

40 million lemmings can't be wrong........can they?

 

[johnherman]

Please post your answer to be part of the knowledge base.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Flybridge]

OK. For general reference, I got the seeds for this from a book, Fast Track to MDX by Mark Whitehorn, Robert Zare and Mosha Pasumansky, published by Springer. It's on Amazon.

Based on the Warehouse cube in Foodmart, the statement is

Sum(Time.CurrentMember.Lag(Iif(Time.CurrentMember.Level is Time.Month, 11, Iif(Time.CurrentMember.Level is Time.Quarter, 3, 0))):Time.CurrentMember,[Measures].[Warehouse Profit]).

The Iif statement is there because of the 3 levels in the Time dimension, so it works out which level you are at and how far back to go to get a 1 year figure. So at the month level it goes back 11 months, at the quarter level it goes back 3 quarters and at the year level it doesn't go back because you are allready looking at an annual figure.

In the book the example is how to create a rolling average. I hope others find this useful.