Hi all,

one of the slowest things our users encounter in querying our cubes is the
'MAT' (Moving Annual Turnover), which is a calculated measure using MDX
basically like:

sum(LastPeriods(52,[Year].CurrentMember.PrevMember),[Measures].[Sales$])

There is some other code to do some checks like - only do it like this if
Year.Level.Name = "Week" etc.

I am guessing this is slow because it has to sum up 52 different periods.
So, I want to make it better.

The Year dim has the levels Year, HalfYear, Qtr, Month, Week. In Week 1, to
get the last 52 weeks, I should be able to just get the value for the
previous year, eg find the year I am in, and parallelperiod 1 period back and
give me the 'year' value. As such, it has only looked at 1 period, rather
than sifting through 52 weeks. Should be faster, no?

Week 33 of the year, for example (middle of Month 8, in the middle of Qtr 3,
which is the start of the Second Half), would be along the lines of:

Give me the last 2 weeks, (to get me to the start of the month), PLUS the
last month, (to get me to the start of the quarter), PLUS last HALF, (now at
start of year, PLUS qtr 4 of last year, plus month 9 of last year, plus the
last 2 weeks of month 8 of last year.

In that example, which might read a little hairy, we have only referenced 8
values, instead of 52.

Basically, I want to be able to put together a calculated member that will
do this type of thing for me in a smart fashion. Problem is, I am not smart
myself, and am struggling to do it. I HAVE seen 'somewhere on the interweb'
an example of how to do this, but silly me, I don't remember where it is!!!

Can anyone help??

Thanks,

Matt.

Possibly the following articles were what you were thinking of
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entr
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

HTH,

Chris


--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Matt Kennedy" wrote:

Hi Chris,

actually this isn't the one - but you have definitely covered the same
scenario I am trying to resolve. I am gonna go play!

Thanks very much for this,
Matt.

"Chris Webb" wrote: