Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to have one single report that can display current month value and relevant 12 months rolling value. What I would like to achieve should look like:
FinMonthYear | Measure | Measure 12M Rolling |
JUL-11 | 72 | 72 |
AUG-11 | 66 | 138 |
SEP-11 | 65 | 203 |
OCT-11 | 91 | 294 |
NOV-11 | 109 | 403 |
DEC-11 | 129 | 532 |
JAN-11 | 109 | 641 |
FEB-11 | 136 | 777 |
MAR-11 | 89 | 866 |
APR-11 | 89 | 955 |
MAY-11 | 65 | 1020 |
JUN-11 | 64 | 1084 |
So I can have the visibility of trend for 12 months rolling.
However, I can only manage to have current month value and 12 months rolling value displayed for one specific year-month such as:
FinMonthYear | Measure | Measure 12M Rolling |
JUN-11 | 64 | 1084 |
If I select one MonthYear period, the rolling value is calculated correctly.
But if I select multiple periods, it can only give me the rolling value for the latest month such as:
FinMonthYear | Measure | Measure 12M Rolling |
JUL-11 | 72 | 1084 |
AUG-11 | 66 | 1084 |
SEP-11 | 65 | 1084 |
OCT-11 | 91 | 1084 |
NOV-11 | 109 | 1084 |
DEC-11 | 129 | 1084 |
JAN-11 | 109 | 1084 |
FEB-11 | 136 | 1084 |
MAR-11 | 89 | 1084 |
APR-11 | 89 | 1084 |
MAY-11 | 65 | 1084 |
JUN-11 | 64 | 1084 |
I have tried a lot of changes in my set analysis script but no luck. Does anyone konw how to get this work without createing a different calendar table? Many thanks in advance.
Set analysis can't help you here. The set is calculated once for the entire chart, not per row. The set is calculated first and then the dimensions and expressions are applied to the set. What you could use instead is what some of us call an AsOf table. See here for an example.
Try this
sum({<FinMonthYear={">$(=Date(Addmonths(Max(FinMonthYear),-12),'MMM-YYYY'))<=$(=DAte(Addmonth(Max(FinMonthYear),0),'MMM-YYYY'))"}>}Measure)
Hi,
Have a look at the attached example.
Regards,
Kaushik Solanki
Set analysis can't help you here. The set is calculated once for the entire chart, not per row. The set is calculated first and then the dimensions and expressions are applied to the set. What you could use instead is what some of us call an AsOf table. See here for an example.
hi,
please find attached,...
hope that will help you..
HTH
sushil
In case anyone interested, here is the solution using island table. Very easy to implement but according to other people in community, it may have performance issue when the data volumes is big. In that case, AsOf table should be the alternative (basically is a pre-calculation in load script process).