jas

04-04-2008, 12:46 AM

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