Calculate Moving (Rolling) Averages (1 Viewer)

[cft]

I need information regarding how to calculate a moving average over the most recent 6 months-to-date. I searched the postings for "Moving" and "Rolling" averages, but came up empty. Any Ideas?

 

[Pat Hartman]

The averages are calculated normally. The only thing you need to do is use selection criteria to select the data from the correct timeframe. Create your query and then add the following Where clause:

Where YourDate Between [Enter Start Date] and [Enter End Date];

The user will be prompted for the two dates. You could also use the Date() and DateAdd() functions if you wanted to calculate the date range on the fly.

 

[cft]

Thank you! This is good information. I am trying to figure out how to use this to automate the between function to read Now() or "Today's Date" and calculate the Average for the past 180 days, or 90 days, etc... Is there any SQL or Expression building for this? I know just enough to know what I want, but not enough to get there. Thanks.

 

[Pat Hartman]

Use Date() rather than Now() since you don't need the time of day. Normally you would use DateAdd() to add or subtract (use a negative number rather than a positive) some time interval. Since the interval you want is days, you can use the following shortcut -

Date() - 180

Days is assumed. If you need to use a different interval, you'll need to use DateAdd() where you can specify whether you are adding days, months, years, etc.

 

[cft]

This works great! Thanks for your input and help!

 

[Cosmos75]

Must Try!

This goes on my must-try list!! Am trying to create a graph that show X weeks cost and show the moving average from week to week BUT it will go like so..

Average of Week 1 through Week 2
Average of Week 1 through Week 3
Average of Week 1 through Week 4
...
Average of Week 1 through Week X

Kinda wierd.

Edit : Feel free to post a solution to my problem if you know it! If I figure it out I'll try to remember to post any solution I've found.

 

[Cosmos75]

Got help and now know how to do it!

Here it is!!

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806