asked on

Moving 3 month moving average with Year and Month numbers only

I am looking to calculate the 3 month moving average but I only have the year and the month numbers (as separate fields in the table) for each monthly spend (dollars).

Data looks something like this:

ReceiptYear ReceiptMonth Spend (just some random numbers for this example)
2009        7        5
2009             8             8
2009             9             5
2009             10             6
2009             11             3
2009             12             6
2010             1             8
2010             2             2
2010             3             6
2010             4              4
2010             5             7
2010             6             1
2010             7             6
2010             8             9

I figured out how to make a date out of the year and month fields:
ReceiptDate: DateSerial([ReceiptYear],[ReceiptMonth],1)
which gives me dates for the first of the month which is fine.

but can't seem to figure out how to get the moving average part working.
Thanks.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SheaJeff

ASKER

Capricorn,
If I insert this criteria, I get no results.

Also, how do I get the average?

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SheaJeff

ASKER

Oops. I didn't have 2011 data.

I inserted 1-11 months for 2011 Year and am getting 9/1-11/1 (3 records).

How do I calculate the moving average for those 3 months?
Also, how can I loop through this to calculate the moving average for all of 2012?
(I know this is building a house of cards, but this is the way I need to do it)

SheaJeff

ASKER

I answered my own question right above with this SQL:

SELECT DISTINCT Avg([spend]) AS AverageSpend
FROM Data
WHERE (((DateSerial([ReceiptYear],[ReceiptMonth],1)) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())-3,1)));

But how can I loop through to roll this forward for 12 months? (building on the previous month's calculated 3 month average)

Jeffrey Coachman

First, there is no one set "Moving Average" formula.
A moving average can be calculated in any number of ways.
They range from simple:
http://chandoo.org/wp/2009/04/28/calculate-moving-average/
...to more complex:
http://www.fourmilab.ch/hackdiet/www/subsection1_4_1_0_8.html

So you will have to be very specific on exactly what you want here.

If what you really need is a simple "Cumulative" ("running") average you can do something like this:

JeffCoachman

untitled.JPG
Access-EEQ27480258-ReportMovingC.mdb

Rey Obrero (Capricorn1)

this criteria

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())-3,1)));

gets you the rolling three months records..

now what do you mean by

<But how can I loop through to roll this forward for 12 months? (building on the previous month's calculated 3 month average)>

SheaJeff

ASKER

Capricorn,
My question can be illustrated with the screenshoot from boaq2000:

Let's pretend today is Jan 1 2011 on his data table:
I want to forecast the 3 month rolling average out until Aug 2011.
This is easy enough based on ACTUAL [prior to today] data, but I want to continue it out 8 months until Aug 2011.
This would mean that the rolling average in the future months is based on the other rolling averages from any future months
for example:
July 2011 is based on June, May, April, but those months are also based on 'future' calculated rolling averages.

What I'm trying to do is this:
Each new month, I need to fill in a monthly "forecast" that goes out until the end of the year.

This is at it's worst in January as I am predicting 12 months, but as the year moves along, more of the "spend" is based on actual (<now), and the forecast is only for the remaining months of the year (>now).

SheaJeff

ASKER

boaq2000,

How can I change your [txtAmountRunSum]/[txtRecCount] formula into a query that will populate a table that will fill in the "rest of the months" of the year going forward

Jeffrey Coachman

Your original question did not say how you wanted to display this data...

You will have to investigate one of the many techniques to do "running values" in queries.

See here for some hints
http://support.microsoft.com/kb/290136

Rey Obrero (Capricorn1)

i believe the original question of this thread has been resolved...

post another q for your other problems..

SheaJeff

ASKER

final solution for moving average of the next month was this:

SELECT DISTINCT Avg([spend]) AS AverageSpend
FROM Data
WHERE (((DateSerial([ReceiptYear],[ReceiptMonth],1)) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())-3,1)));

I will post another question to clarify my original question about forecasting future values with the moving average.

Thanks for the help.