I have a requirement where a user would like to see a 100% growth rate if the first period's dollar figure is 0 vs the current period which has something >0. (which I know is a magical number pulled out a rainbow).
September | October | Percentage |
0
| 5 | 100% |
I can see doing this in a relational report, however, my brain is melting attempting to figure it out dimensionally.
Here is what I am using currently to get a percentage based off the dimensions I use
( ( (tuple (currentMember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods]),[Dollars Sold]))
-
(tuple (prevMember(currentmember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods])),[Dollars Sold]) ) )
/ abs( (tuple (prevMember(currentmember([DLPlus Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods])),[Dollars Sold]) ) ))
Is there a technique to accomplish what I'm asking from within report studio???
Two ways.
Generally when I show growth I use the following expression:
(Current Period / Last Period) - 1
If the last period is 0, then you'd get a div zero error. In that case, simply set the data format to show 100% for div zero errors.
Alternatively, you can use an IF statement inside the expression:
if(Last Period = 0) then (1) else ((Current Period/Last Period)-1)
Thank you Paul!!!!
One more question for you is how do I handle two consecutive periods that have zero in them? Because that behavior would put those values to 100% as well?
I think I answered my own question. Paul, thank you again!!!
Here is what I used:
if ((tuple (prevMember(currentmember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods])),[Dollars Sold]) ) = 0 and
(tuple (currentMember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods]),[Dollars Sold]))
>0)
then (1)
else
(
( ( (tuple (currentMember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods]),[Dollars Sold]))
/
(tuple (prevMember(currentmember([Invoiced Cases Cube].[Billing Period].[Last 12 Rolling Billing Periods])),[Dollars Sold]) ) ) )
- 1)