Rolling 12 months based on Division

[mphekin12]

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

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:

Can anyone at all help me with my formula?

Thank you so much!

[#2]

Hi,

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

Regards

Mario

[mphekin12]

Mario,

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

Any other suggestions?

[Gysbert_Wassenaar · Accepted solution]

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

[mphekin12]

I'll look into that Gysbert.

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

[#6]

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]

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

[mphekin12]

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]

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]

Thanks Christian.