Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mphekin12
Specialist
Specialist

Rolling 12 months based on Division

I'm trying to set up a formula to get the rolling twelve month of sales.  So far, this is what I have:

Capture.PNG.png

The problem is, once there is a new division, those sales numbers should not be counted towards the previous division's 12 month sum.  In the chart above Alexandria's numbers are wrong for most of 2011.  They should be:

Capture2.PNG.png

Can anyone at all help me with my formula?

Thank you so much!

1 Solution

Accepted Solutions
9 Replies
Not applicable

Hi,

try to add an Aggr function by D.Division in the expression.

Regards

Mario

mphekin12
Specialist
Specialist
Author

Mario,

Thanks for your response.  I did try that and got the following:

Capture3.PNG.png

Any other suggestions?

Gysbert_Wassenaar

Try an AsOf table: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
mphekin12
Specialist
Specialist
Author

I'll look into that Gysbert.

Do you know of any way I could do this in the LOAD script?

Not applicable

Hi,

Here a document that may help you:

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

Gysbert's one is very good : I understood the method thanks to it.

Fabrice

mphekin12
Specialist
Specialist
Author

Thank you Aunez, I will take a look at your document.

mphekin12
Specialist
Specialist
Author

I jus wanted to thank Aunez and Gysbert for their posting their time analysis documents.  If anyone is looking for ways to easily calculate rolling periods, these documets are a must read.

I'm sorry that I can mark both of the them as correct.

christian77
Partner - Specialist
Partner - Specialist

Hi.

There is another way to calculate mobile ratios. That can be avg mobile, TAM, etc.

It consists in looping your resident base load from the fact table and adding those totals and, besides, grouping your dimensions.

This process can be long depending on your dimensions and how many groups they create. The thing is that when you have it loaded, it's just another field associated to the month and that's it.

Mobile averages are meant to extract the cyclic component of a time series. Nowadays TAM is used to extract the cyclic component within a year, which means, the seasonal component, confused with a cyclic component. Well, they are different things but it works anyway because every time you add 12 rolling months you are adding a month of each, and theoretically, the sum should be similar.

mphekin12
Specialist
Specialist
Author

Thanks Christian.