Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to calculate a sum for a rolling period (e.g.; last 7 days)?

8 views
Skip to first unread message

Steven H

unread,
Aug 23, 2006, 11:40:01 AM8/23/06
to
If we have a measure that is number of sales orders per day [Sales Order
Count], how would we write an MDX statement that will return the sum of
Sales Order Count for the last "X" days, such that if the cutrrent member is
Monday 8/21/2006 and we want the last 7 days sun, we get sum of Sales Order
Count from 8/15/2007 - 8/21/2006?

Thanks!


Jéjé

unread,
Aug 23, 2006, 12:39:24 PM8/23/06
to
something like:
sum(
{time.currentmember.lag(7):time.currentmember}, measures.[Sales Order
Count])

"Steven H" <steven...@ilg.com> wrote in message
news:OBjtmpsx...@TK2MSFTNGP03.phx.gbl...

ZULFIQAR SYED

unread,
Aug 23, 2006, 9:03:42 PM8/23/06
to
Steven,

Please try LASTPERIODS function in case you are using AS2005. Here is a
test script against Adventure Works DW in the samples.

with
member measures.x as
sum(
{
lastperiods(
7,
[Date].[Calendar].[Date].&[629]
)
}
,
[Measures].[Internet Sales Amount]
)
select
{
lastperiods(
7,
[Date].[Calendar].[Date].&[629]
)
} on 1
,
{
[Measures].[Internet Sales Amount]
,
measures.x
}
on 0
from
[Adventure Works]

HTH..

ZULFIQAR SYED
HTTP://ZULFIQAR.TYPEPAD.COM

Jeje

unread,
Aug 23, 2006, 9:35:46 PM8/23/06
to
good, this is the function I'm looking for but my memory has decide to not
help me ;-)
lastperiods is more easy to use and to read.

"ZULFIQAR SYED" <dat...@gmail.com> wrote in message
news:1156381422.2...@75g2000cwc.googlegroups.com...

0 new messages