11 Replies Latest reply on Feb 5, 2014 3:13 AM by Gorazd Ozimek

# Moving annual totals

Hi

I have a data set which shows the sales of a product by month, what I need to do is create a moving annual total by month and put these results into a table, so for example November's Moving Annual Total would be the sum of November's sales and the 11 months prior to be a full annual total. I would like to put these into a table by month and as I update the data with the latest month it populates the moving annual total table with the newest month.

Would anyone know of a calculation that would help with this?

I have attached a sample of my data in a workbook.

Many thanks

Chris

• ###### 1. Re: Moving annual totals

I am not sure what you would want for the start/end range for a moving sum, so I am guessing you are looking for a running sum as in the attached.

• ###### 2. Re: Moving annual totals

Thanks Joe

For the moving sum I would require the latest month within the dataset for the end range, for the start range I would want the sum of 11 months prior to this data to make up a full year.

So for example

October 2011 moving annual total = Sum of November 2010 to October 2011

November 2011 moving annual total  = Sum of December 2010 to November 2011

December 2011 moving annual total = Sum of January 2011 to December 2011

I wanted to display these totals within a table like

October MAT                November MAT                    December MAT

10,000                            12,000                                  11,500

Thanks again

Chris

• ###### 4. Re: Moving annual totals

Perfect!

thanks Joe

• ###### 5. Re: Moving annual totals

Hi Joe

How do I add a calculation that compares the same MAT month to last year? i.e. Sep 10 MAT compared to Sep 11 MAT increase of 10,000 units or 7% etc.

Many thanks

Chris

• ###### 7. Re: Moving annual totals

Thanks Joe,

How do I get the same calculation to appear for the other months also?

Chris

• ###### 8. Re: Moving annual totals

For your sample data, the other months would be be comparing 12 months vs less than 12 months of data, so I figured it would be better to not show a number for the other months because they would be misleading numbers.

I have the table calculations setting set to return Null if not enough values.

• ###### 9. Re: Moving annual totals

Good point, thanks for your help, When I update the dataset with the next month, will the calculation populate the October month?

• ###### 10. Re: Moving annual totals

Hi Joe,

Going back to my previous worksheet that you sent - how do I just display the most recent month MAT and display it next to the previous years MAT of the same period. i.e. MAT Oct 2010 vs Mat 2011?

Many thanks

Chris

• ###### 11. Re: Moving annual totals

Hi,

I would also like to know the answer to Christopher's last question. Any hints?

Regards,

Gorazd