Moving Sum doesn't seem to work

Last Post 03 Aug 2007 12:20 AM by VoiceOver. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
VoiceOver
New Member
New Member

--
02 Aug 2007 10:52 PM
I need as a calculated measure in a cube a moving sum over a year, i.e.
For quarter 2007_1, would need the sum of quarters 2006_2, 2006_3, 2006_4 and 2007_1; for quarter 2007_2 I would need sum of quarters 2006_3, 2006_4, 2007_1 and 2007_2
etc.
As a base for the function I used the moving average function:
AVG({[time].[Quarter].Lag(3) : [time].[Quarter].CurrentMember}, [Measures].[Sales])
and simply replaced it with
SUM({[time].[Quarter].Lag(3) : [time].[Quarter].CurrentMember}, [Measures].[Sales])

The result however to my surprise was a YTD result, i.e. a cumulative sum per year, meaning
2007_1 would give me the sales of 2007_1; 2007_2 the sum of sales for 2007_1 and 2007_2 and not the quarters as mentioned above.
Why didn't it work?
SQLUSA
New Member
New Member

--
02 Aug 2007 11:32 PM
What if you multiply the the moving average by COUNT ?

Kalman Toth, OLAP Architect
SQL Server 2005 Training - http://www.sqlusa.com
VoiceOver
New Member
New Member

--
03 Aug 2007 12:20 AM
Thanks for the input SQLUSA, but it's not that I didn't find a workaround, I did, by simply adding the values of the previous quarters seperately.
My question is why didn't the expression work, since it would have been way more efficient to use the Lag-function.


Acceptable Use Policy
---