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.
SheaJeffAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


ReceiptDate: DateSerial([ReceiptYear],[ReceiptMonth],1)



Criteria   Between dateserial(year(date()),month(date()),1) and dateserial(year(date()),month(date())-3,1)
0
 
SheaJeffAuthor Commented:
Capricorn,
If I insert this criteria, I get no results.

Also, how do I get the average?
 screen shot
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<If I insert this criteria, I get no results.>

do you have records that have the year 2011 and months  9,10,11
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SheaJeffAuthor Commented:
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)
0
 
SheaJeffAuthor Commented:
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)
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:




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)>




0
 
SheaJeffAuthor Commented:
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).
0
 
SheaJeffAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:
i believe the original question of this thread has been resolved...

post another q for your other problems..
0
 
SheaJeffAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.