Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reporting over multiple rolling 12 month periods

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:

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

2) User selects month end  31/12/2011

My table should display:

YearCalculation
2011Rolling 12 month sum from 01/01/2011 to 31/12/2011
2010Rolling 12 month sum from 01/01/2010 to 31/12/2010
2009Rolling 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.

4 Replies
alexandros17
Partner
Partner

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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