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!
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.
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.