calculating a rolling average of quarterly values

[Paul Ponzelli]

I have a Total query that uses the following expressions to obtain quarterly
values:

Group By quarter:
Quarter: DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval])

and take the quarterly Averages of this value:
DaysToApprove: DateDiff("d",[Filed],[Approval])

Those two expressions produce the desired result for DaysToApprove. Now I
need to build another expression that would calculate a rolling average of
the last four quarters of DaysToApprove. That is, in any quarter, I would
like this expression to obtain the average of the current quarter and the
last three quarters.

Can anyone give me an idea of how I can build this expression?

Thanks in advance,

Paul

 

[Michel Walsh]

Hi,


If you can change you Group by to:

QuarterFirstDate: DateSerial( DatePart("yyyy", approval),
3*DatePart("q", approval), 1 )


if that query is saved under the name Qsaved, then, something like:

SELECT AVG(AverageOfDaysToApprove)
FROM Qsaved
WHERE QuarterFirstDate BETWEEN
DateSerial( DatePart("yyyy", Date()), 3*DatePart("q", Date()),
1 )
AND
DateSerial( DatePart("yyyy", DateAdd( "q", -4, Date())),
3*DatePart("q", DateAdd( "q", -4, Date()), 1 )



Note that the average of the average is not the same as the average of the
covered set. Numerical example:

{ {1} , {1}, {1}, { 2, 2, 2} } == the original set, its average = 9 / 6
= 1.50

the average of each subset is: 1, 1, 1, 2 and the average of these
averages is 5/4 = 1.25. Clearly, 1.25 <> 1.50


I assumed you wanted the average of the averages.



Hoping it may help,
Vanderghast, Access MVP

 

[Paul Ponzelli]

Thanks for your suggestion, Michael, and also for the interesting point you
made about taking averages of subsets.

You are absolutely right that in general, the average of the averages will
produce a different result than taking an direct average of all the members
of the subset. However, there is one set of cases where the two averages
are always equal - when the number of elements in each subset are equal.
Using the elements from your example,

Average(1,1,1) = 1
Average(2,2,2) = 2

and both the average of the averages and the direct average of the subset
yields 1.5.

In the case of my query, that's exactly what I'm doing. In all cases, I'm
taking the average of four quarters, and my rolling averages also takes the
average of the four most recent averages.

Prior to reading your message this afternoon, I had managed to design a
two-query solution that produces the desired result:

Query 1: qryDaysToApprove

SELECT tblApplication.Action, tblApplication.InstitutionType,
DateDiff("d",[Filed],[Approval]) AS TotalDays, IIf(Not
IsNull([Reactivated]),DateDiff("d",[Suspended],[Reactivated]),0) AS
SuspendedDays, [TotalDays]-[SuspendedDays] AS ApprovalDays,
tblApplication.Applicant, tblApplication.Filed, tblApplication.Approval,
tblApplication.Suspended, tblApplication.Reactivated
FROM tblApplication
WHERE (((tblApplication.Action)="approved") AND
((tblApplication.InstitutionType)="Bank" Or
(tblApplication.InstitutionType)="Industrial Bank") AND
((DateDiff("d",[Filed],[Approval]))>0) AND
((tblApplication.Application)="new charter"))
ORDER BY tblApplication.Approval;

Query 2: qryQtrAverage

SELECT DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval]) AS
Quarter, Avg(qryDaysToApprove.ApprovalDays) AS AvgApproval,
Val(IIf([quarterID]>3,DAvg("AvgApproval","qryQtrAverage","quarterID <= " &
[quarterID] & " AND quarterID >= " & [quarterID]-3),"")) AS 4QtrAvg,
Count(qryDaysToApprove.Action) AS CountOfAction,
First(DLookUp("quarterID","tblQuarterID","quarterTxt = '" & [Quarter] &
"'")) AS quarterID
FROM qryDaysToApprove
WHERE ((Not (qryDaysToApprove.Approval) Is Null))
GROUP BY DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval])
ORDER BY DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval]);

I tested these two queries over several different data sets, and it works
just fine. However, I'm also going to try out your suggestion as an
alternate approach and learning experience. Your use of the DateSerial()
function is interesting, and it looks like something that would be of great
help with some other queries that I'm trying to build.

Again, thank you for taking the time to produce a creative approach for
handling dates in queries, and getting me to take a look at what looks like
a powerful new (to me) function.

Paul