Does anyone have a function for calculating a 12 month moving average? For example there are 2 fields in a table:
F1. Date c 8 always entered as 1st day of the month
F2. Sales n 16 2 total sales for the month defined by the date field
There are 24 records in a table:
Date Value for sales
1. 12/01/2005 1000.00
2. 01/01/2006 2000.00
3. 02/01/2006 3000.00
4. 03/01/2006 4000.00
5. 04/01/2006 5000.00
6. 05/01/2006 6000.00
7. 06/01/2006 7000.00
8. 07/01/2006 8000.00
9. 08/01/2006 9000.00
10. 09/01/2006 10000.00
11. 10/01/2006 11000.00
12. 11/01/2006 12000.00
13. 12/01/2006 13000.00
14. 01/01/2007 14000.00
15. 02/01/2007 15000.00
16. 03/01/2007 16000.00
17. 04/01/2007 17000.00
18. 05/01/2007 18000.00
19. 06/01/2007 19000.00
20. 07/01/2007 20000.00
21. 08/01/2007 21000.00
22. 09/01/2007 22000.00
23. 10/01/2007 23000.00
24. 11/01/2007 24000.00
If I understand correctly the 12 month moving average starting with Nov 2007 would be calculated as follows:
Nov 2007 = total sales for records 13-24 / 12
Oct 2007 = total sales for records 12-23 / 12
Sep 2007 = total sales for records 11-22 / 12
And so forth.
I know this must seem very simplistic to many of you, but I need to be very sure that I understand it correctly and am expressing it properly. I need to build a report to show the 12 month moving average for not 1, but 30 different parameters spanning a time period beginning some time in 1995 through April 2008. I also need to do other similar reports showing the 12 month moving average for different expressions such as “Gross Sales” divided by “Labor Costs” expressed as a percentage or total ”Gross Sales “ of one company compared to total “Gross Sales” of ALL other companies in the table expressed as a percentage!. If the above example is correct, then I’ve got a report to work for 1 parameter by using 24 sub-reports and a bunch of calculated fields and that is unbelievably archaic and time consuming. I wasn’t anywhere near the top ten in my math classes in college, so I really do need some help and/or advice on this. It seems to me that it should be possible to build a function that would define a query for a report to show a moving 12 month average for ALL parameters, but so far I’m drawing a blank.
As usual, any help will be greatly appreciated.
Thanks
JAS
F1. Date c 8 always entered as 1st day of the month
F2. Sales n 16 2 total sales for the month defined by the date field
There are 24 records in a table:
Date Value for sales
1. 12/01/2005 1000.00
2. 01/01/2006 2000.00
3. 02/01/2006 3000.00
4. 03/01/2006 4000.00
5. 04/01/2006 5000.00
6. 05/01/2006 6000.00
7. 06/01/2006 7000.00
8. 07/01/2006 8000.00
9. 08/01/2006 9000.00
10. 09/01/2006 10000.00
11. 10/01/2006 11000.00
12. 11/01/2006 12000.00
13. 12/01/2006 13000.00
14. 01/01/2007 14000.00
15. 02/01/2007 15000.00
16. 03/01/2007 16000.00
17. 04/01/2007 17000.00
18. 05/01/2007 18000.00
19. 06/01/2007 19000.00
20. 07/01/2007 20000.00
21. 08/01/2007 21000.00
22. 09/01/2007 22000.00
23. 10/01/2007 23000.00
24. 11/01/2007 24000.00
If I understand correctly the 12 month moving average starting with Nov 2007 would be calculated as follows:
Nov 2007 = total sales for records 13-24 / 12
Oct 2007 = total sales for records 12-23 / 12
Sep 2007 = total sales for records 11-22 / 12
And so forth.
I know this must seem very simplistic to many of you, but I need to be very sure that I understand it correctly and am expressing it properly. I need to build a report to show the 12 month moving average for not 1, but 30 different parameters spanning a time period beginning some time in 1995 through April 2008. I also need to do other similar reports showing the 12 month moving average for different expressions such as “Gross Sales” divided by “Labor Costs” expressed as a percentage or total ”Gross Sales “ of one company compared to total “Gross Sales” of ALL other companies in the table expressed as a percentage!. If the above example is correct, then I’ve got a report to work for 1 parameter by using 24 sub-reports and a bunch of calculated fields and that is unbelievably archaic and time consuming. I wasn’t anywhere near the top ten in my math classes in college, so I really do need some help and/or advice on this. It seems to me that it should be possible to build a function that would define a query for a report to show a moving 12 month average for ALL parameters, but so far I’m drawing a blank.
As usual, any help will be greatly appreciated.
Thanks
JAS
Comment