Hello,

Currently I am pulling my hairout trying to write some KPIs for AS 2k5.
What I am trying to do is return an average against a measure for the
last 7 days and 56 days respectively. Firstly, I trying completing the
tasking with the following MDX & then using a Calculated Member.

I was using the below MDX expression (which changes appropriately) &
filtering by the current date:
Avg
(
[Date].[CalendarDate].CurrentMember.Lag(56) :
[Date].[CalendarDate].CurrentMember,
[Measures].[Withdraw Txns]
)

I have also tried executing the TAIL function on the appropriate data
.... alas, no love.

Unfortunately, the KPI engine decides to sum the set of data it
recieves, which royally buggers me.

Basically, does anyone know how to create a KPI that will always return
an average of last 7 days from the current day (so the user doesnt
always have to filter the data). This is killing me so any help is
most gratefully accepted.

Regards,
James Kent

James,

This problem is not really unique to KPI's, it occurs with any measure
that revolves around some concept of the "current date". The answer to
your issue depends on how you want your calculation to behave when the
Date dimension is at a level other than the day level.

If someone does a query with the year members on one of the axis, your
current calculation will attempt to average the last 56 years.

Something like...

AVG(TAIL(DESCENDANTS([Date].[CalendarDate].CurrentMember,[Date].
[CalendarDate].[Day]),7), [Measures].[Withdraw Txns])

What do you want the calculation should do when viewed at a higher
level, like a year level? The most common approaches would be to get the
last 7 days of the year using the descendants() and tail() functions. Or
to get the last 7 non-empty days of the year by using a nonempty
crossjoin.

Something like...

AVG(TAIL(NONEMPTYCROSSJOIN(DESCENDANTS([Date].
[CalendarDate].CurrentMember,[Date].[CalendarDate].[Day]),{[Measures].
[Withdraw Txns]}),7), [Measures].[Withdraw Txns])


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1136342252.325910.80780 (AT) g49g2000cwa (DOT) googlegroups.com>,
james (AT) roadhouse (DOT) com.au says...