Reporting over multiple rolling 12 month periods

[#1]

Hello

I have a requirement to create an expression to report over multiple rolling 12 month periods based upon a month end date selected by the user.

Two example scenarios below.

1) User selects month end 31/03/2012.

My table should display:

Year	Calculation
2012	Rolling 12 month sum from 01/04/2011 to 31/03/2012
2011	Rolling 12 month sum from 01/04/2010 to 31/03/2011
2010	Rolling 12 month sum from 01/04/2009 to 31/03/2010

2) User selects month end  31/12/2011

My table should display:

Year	Calculation
2011	Rolling 12 month sum from 01/01/2011 to 31/12/2011
2010	Rolling 12 month sum from 01/01/2010 to 31/12/2010
2009	Rolling 12 month sum from 01/12/2009 to 31/12/2009

So, depending on the month end selected by the user, the table should display the rolling 12 month sum of my measure, along with the same calculation for the same period in the preceeding two years.

Is this possible with set analysis?

Thanks in advance for any assistance.

[alexandros17]

It's possible to do this by set analysis but it is easier, when you load data, to compute foe each month end other columns related to the rolleng period you desire for instance column1: start of the first rolling period, column2: end of the first rolling period.

In this way you can easily use set analysis simply comparing date

Hope it helps

[Gysbert_Wassenaar]

Yes, that's possible with three expressions. Something like:

sum({<[MyDate]={">$(=addyears([MyDate],-1))<=$(=only([MyDate]))"}>}Sales)

sum({<[MyDate]={">$(=addyears([MyDate],-2))<=$(=addyears([MyDate],-1))"}>}Sales)

sum({<[MyDate]={">$(=addyears([MyDate],-3))<=$(=addyears([MyDate],-2))"}>}Sales)

Add those to a straight table, don't add any dimensions and check the option Horizontal on the Presentation tab.

[#4]

Thanks for the suggestion Gysbert.  I did manage to get this working and it worked well, but I think i may need to go down the pre-computed in the script route.  The reason being, that really i need to combine the three seperate expressions into a single one; I have more expressions to build into the same table table which complicate things a little, especially when the table is made horizontal.

[#5]

Hi Alexandros.  Thanks, this was very helpful.  I have found some good examples using a AsOf table to achieve similar results, but i prefer the idea of using two columns to bound the start and the end of the rolling periods I need.  I am working with three year rolling periods, and to generate all the date combinations for these sounds onerous.

Please could you elaborate a little on how your approach would work?

Thanks