# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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:

 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.

4 Replies
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

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

Community Browser