PDA

View Full Version : Function for Moving Average...


ABC123

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

jkletrovets
04-04-2008, 12:08 PM
James


Here is one idea...

Take a look at the tablesum() (http://support.alphasoftware.com/alphafivehelp/Functions/TABLESUM().htm) and tablecount() functions in the help.

Table sum can take a filter and you could just use something like:

sales_date>=(Date()-365) as the filter. This will get you all records within the last year.

Use the same filter for tablecount(). With a combination of those two functions I think you can get where you want.

Regards,

Jeff

Tom Cone Jr
04-04-2008, 12:19 PM
Jeff, good idea. Might TableAvg() be even more appropriate?

jas
04-04-2008, 12:21 PM
Thanks Jeff.

I won't be able to work on this til tomorrow, but the functions do look like good possibilities. I also ran accross TABLEAVG() which I might be able to use in conjunction with the other two.

jkletrovets
04-04-2008, 12:52 PM
Might TableAvg() be even more appropriate?

Well by golly....it sure might be. :D

I don't know why I forgot about that function! :confused: