Calculating Rolling 12 Months for charting
We track important company metrics in a file with separate tables going back several years for each metric. (sales, New Customers, free trials, etc.) Each record in these tables contains the date, (year and month), the product and value of the metric.
To remove the effect of seasonality, we'd like to chart these on a rolling 12 month basis but I'm not sure how to do this. The datapoint for each month is the sum of 12 records but the group being summed shifts each month. - adding the most recent month and dropping the oldest.
For example, assuming we are tracking Sales.
the RTM (rolling 12 month) value for Jan 2014 would be the sum of Feb 2013 thru Jan 2014. The value for Feb 2014 would be the sum of Mar 2013 thru Feb 2014. And so on.
We have the individual monthly values but how can we create a chart plotting these RTM values?