Sorry if this is a simple question, but I'm very new at OLAP.

In SQL 2005 I am trying to calculate a 12-month moving average for a measure
in my cube and I'm having a little trouble. The average seems to reset
itself as it crosses boundaries in the time hierarchy.

I'm using the following calculation in the cube to get my 12-month rolling
average:

Avg ([Time].[CalendarMonth].CurrentMember.Lag(12) :
[Time].[CalendarMonth].CurrentMember,
[Measures].[Revenue Actual])

My cube looks like this:

Time Dimension:
*CalendarYear
**CalendarQuarter
***CalendarMonth

Fact_Revenue
Fulldate, Program_Name, Revenue_Actual, Revenue_Plan


When calculating the averages, it resets itself as soon as it passes a YEAR
or QUARTER boundary. For example, if I look at (2004, Q1, Month 3) it will
show a correct average for only the 2004 Q1 Months 1,2 and 3. It is not
able to figure in the averages of values that fall in a different quarter or
year.

I'm not sure if this is an error with my MDX expression, my Time dimension,
or what. Does anyone have any suggestions on how to do this?

Thanks,

-s

Hi Stephen

What version of AS do you have?
Is [Time].[CalendarMonth] a hierarchy level or a hierarchy?

Thanks,
Vladimir Chtepa

"Stephen Sanders" <fake (AT) fake (DOT) com> schrieb im Newsbeitrag
news:Og76mEeHGHA.1100 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

I am using Analysis Server 2005 that was installed with SQL 2005.
[Time.CalendarMonth] is a hierarchy level. My complete time dimension is
like this:

[TIME]
*[CalendarYear]
**[CalendarQuarter]
***[CalendarMonth]
****[FullDate]

Thanks,

-s


"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote

You should take current member on the hierarchy not on the hierarchy level.
How many hierarchies do you have in the time dimension?

Please, look at this example, that i write for AW sample database.

with

member [measures].[glidingavg] as
avg([Date].[Calendar].currentmember.lag(12):[Date].[Calendar].currentmember,
[Measures].[Internet Sales Amount])

select
{[Measures].[Internet Sales Amount], [measures].[glidingavg]} on 0,
{[Date].[Calendar].[Month].members} on 1
from [Adventure Works]

Vladimir Chtepa

"Stephen Sanders" <fake (AT) fake (DOT) com> schrieb im Newsbeitrag
news:%237y0WuQIGHA.2948 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

That worked nicely! Thank you for your help.

-s

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote