12 months rolling for multiple selection of months on one single report

[#1]

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.

[er_mohit]

Try this

sum({<FinMonthYear={">$(=Date(Addmonths(Max(FinMonthYear),-12),'MMM-YYYY'))<=$(=DAte(Addmonth(Max(FinMonthYear),0),'MMM-YYYY'))"}>}Measure)

[kaushiknsolanki]

Hi,

     Have a look at the attached example.

Regards,

Kaushik Solanki    

[Gysbert_Wassenaar · Accepted solution]

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.

[sushil353]

hi,

please find attached,...

hope that will help you..

HTH

sushil

[#6]

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).