How to calculate a Rolling Average

[AJ]

I am having difficulties building a rolling average, and hoping that somebody
out there knows how to do it............
I've tried building a query that will select the data from the previous
quarter (DatePart ("q",-1,[Date])), however, this method does not allow you
to retrieve 4th quarter data. Is there some sort of code that is spepcific
to quarters?

My rolling average will include values of the following quarters: 2 (2009),
1 (2009), 4 (2008), 3 (2008)..... and I need this to roll forward every
quarter. Any ideas are appreciated!

 

[vanderghast]

If you want the average per quarter, you can use:


SELECT datediff("q", yourDateField, now), AVG(dataToAverage)
FROM somewhere
GROUP BY datediff("q", yourDateField, now)


you can add the first date and the last date, in your data, for each
quarter:


SELECT datediff("q", yourDateField, now) AS qNumber,
MIN(yourDateField) AS firstDate,
MAX(yourDateField) AS lastDate,
AVG(dataToAverage) AS averageForThisQ
FROM somewhere
GROUP BY datediff("q", yourDateField, now)


If you want the average of your data for, say, the last four quaters (which
is not the same as average of averages), the easiest way is to have a table
with the starting date of each quarter you want,

Quarters ' table name
EndingQ ' field name
#3/1/2009#
#1/1/2009#
#10/1/2008#
.... 'data sample



SELECT
AVG(dataToAverage) AS averageFor4Q,
EndingQ-1 AS ForQuarterEndingThe
FROM quarters AS q INNER JOIN yourTable AS a
ON a.yourDateField < q.EndingQ
AND a.yourDateField >= DateAdd("yyyy", -1, q.EndingQ)
GROUP BY EndingQ-1



should do.



Vanderghast, Access MVP

 

[KARL DEWEY]

These prompts for last quarter to include.
Quarter Start:
DateAdd("q",[Forms]![YourForm]![Frame0]-4,DateSerial(Year(Date()),1,1))

Quarter End:
DateAdd("q",[Forms]![YourForm]![Frame0],DateSerial(Year(Date()),1,1)-1)