February 16, 2005 at 1:10 am
I am trying to investigate how to come up with number on the report that uses the current measure and its past 3 months, 6 months and 12 months interval measure value change.
for example: Current months (Dec) sale = 100 and I want to be able to get to (Sept) sale = 120, (June) sale = 110, (last year's Dec) sale = 150. and add all these period number up.
Do you know how I should define this?? Calculated cell? Using tuple or set?? I need some light on what MDX query would kinda look like...
Thanks for your helps!
JON
DBA/OLAP Developer -
San Francisco, CA
February 16, 2005 at 8:48 pm
in psuedo code, for a calc membr in the measures dim, you could use something like
time.currentmember + time.currentmember.lag(-3) + time.currentmember.lag(-6) + time.currentmember.lag(-12)
but this will cause you problems at levels higher than month, so you may want to put an IIF in the front to check the curent level of the time dim and change the function appropriately.
Steve.
February 17, 2005 at 2:33 am
Create a calculated member in the Sales cube of the Foodmart 2000 DB and use the following construct :
IIF
([Time].CurrentMember.Level.Name="Month", ([Measures].[Unit Sales], [Time].CurrentMember.lag(3)), Null)
1. The lag function should be used with positive numbers to go back in time otherwise it would be equivalent to the Lead function.
2. The formula above gives you the measure for the 3rd month back. Extrapolate to create the others. You can have a single measure or do it in steps : One formula for a 3 month Lag, one for for a 6 month Lag, and so one. The last formula would be the sum of them.
HABIB.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply