Rolling 12 Month Aggregation

[#1]

Hello,

I have a request to create a rolling 12 month average of sales.  Our dashboard displays data back to Jan of 2012.  The client would like to see what the rolling 12 month average was for Jan 2012 with out displaying any data prior to that date.  That means I have to collect data from Jan 2011 through Dec 2011 and aggregate it all into a single value and reference it with the date Jan 2012.  Then February 2012 would be Feb 2011 through Jan 2011 rolled up and referenced with the date Feb 2012.  Can someone please help me understand how qlikview should handle this?

Thank you!

Anthony

[ashfaq_haseeb]

Hi,

Have a look at below post.

http://community.qlik.com/docs/DOC-4252

Regards

ASHFAQ

[#3]

Thanks Ashfaq,  that's close but about half way through that post lies my problem ...

"For the three month periods needed for a rolling 3 month total this means a month should be linked to itself, the previous month and the month before the previous month. The only exceptions are the first month, which is itself the rolling 3 month period, and the second month that together with the first month is its rolling 3 month period. There are no months before the first month so the first two months cannot run over 3 months."

I need the first month on display to reach into historical and calculate the actual 3 month average, not just itself.  I cannot just extend the calendar back further into history either because the client has no interest in seeing that history on the app. 

[Gysbert_Wassenaar]

If your document does not contain the historical data then that data cannot be displayed or used in calculations.

If it does contain historical data then the first month of the complete data set including historical data is linked only to itself. The first month in the complete data set you load in your document is not necessarily the first month you show in your chart. What the first month in your chart is is determined by the selections and expressions you choose to use.

[#5]

The document does and must contain historical data beyond what is displayed in UI.  Set analysis will not work for this because set analysis uses the data present in the UI and they don't want a calendar that goes into history.  So, historical data is loaded but then it's indexed and aggregated into the time period they wish to display.  My original solution uses a historical load, an AsOfTable and a current date load with a loop: i from 1 to 12.  Then I drop the historical table.  It's a bit more complex than that but it works.  I was hoping there was a standard way of dealing with this problem.  If not I will post on how I did it.