Rolling Average Calculation

  • Thread starter Thread starter toby131
  • Start date Start date
T

toby131

I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and
%Incidents.
% Incidents is being calculated as Incidents/Opportunities. I want to add
to this a three month average % Incidents. This would be the Sum of
Incidents for the listed month & previous 2 months / Sum of the Opportunites
for the same time period. It is not critical to me that the first 2 months
in my data set show a meaningful number, since they will not have 2 months
before them. Example of what I am trying to produce. Thanks in advance for
any help with this calculation!

Month %Incidents Incidents Opportunities 3MonthAvg%
2008-01 17.05% 22 129 17.05%
2008-02 22.58% 28 124 22.58%
2008-03 22.12% 25 113 20.49%
2008-04 12.09% 11 91 19.51%
2008-05 10.64% 15 141 14.78%
2008-06 13.99% 20 143 12.27%
2008-07 14.55% 16 110 12.94%
2008-08 20.29% 28 138 16.37%
2008-09 15.83% 22 139 17.05%
2008-10 16.66% 20 120 17.63%
2008-11 13.93% 17 122 15.49%
2008-12 11.54% 12 104 14.16%
 
Assuming the field [Month] is a full date field, not a string, such as
2008.01.01 (supplying the first of the month as day part):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



Since it is a inner join, you can move the ON clause to the WHERE clause
(which makes the query editable in the graphical view):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



In any cases, the magical number 2 comes from that we need to add 2 month,
to a date, to reach the third month first.


Vanderghast, Access MVP
 
Thank you! I used the the WHERE clause version so that it was easy to go
back and add other fields and other administrators that do not use much code
can see the design view. Is there a way to add another rolling average for a
different time period to this same query or do I need to make a seperate
query using this same code and just changing the "2" to the appropriate
number for my new time period?

vanderghast said:
Assuming the field [Month] is a full date field, not a string, such as
2008.01.01 (supplying the first of the month as day part):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



Since it is a inner join, you can move the ON clause to the WHERE clause
(which makes the query editable in the graphical view):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



In any cases, the magical number 2 comes from that we need to add 2 month,
to a date, to reach the third month first.


Vanderghast, Access MVP



toby131 said:
I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and
%Incidents.
% Incidents is being calculated as Incidents/Opportunities. I want to add
to this a three month average % Incidents. This would be the Sum of
Incidents for the listed month & previous 2 months / Sum of the
Opportunites
for the same time period. It is not critical to me that the first 2
months
in my data set show a meaningful number, since they will not have 2 months
before them. Example of what I am trying to produce. Thanks in advance
for
any help with this calculation!

Month %Incidents Incidents Opportunities 3MonthAvg%
2008-01 17.05% 22 129 17.05%
2008-02 22.58% 28 124 22.58%
2008-03 22.12% 25 113 20.49%
2008-04 12.09% 11 91 19.51%
2008-05 10.64% 15 141 14.78%
2008-06 13.99% 20 143 12.27%
2008-07 14.55% 16 110 12.94%
2008-08 20.29% 28 138 16.37%
2008-09 15.83% 22 139 17.05%
2008-10 16.66% 20 120 17.63%
2008-11 13.93% 17 122 15.49%
2008-12 11.54% 12 104 14.16%
Click to expand...
Click to expand...
 
I assume this is possible, yes: add the table a third time, with another
alias, say, c, and add the condition:

a.[Month] >= c.[Month] AND a.[Month] <= DateAdd("m" , 3, c.[Month] )

as example.

I don't say that it will be fast, though.



Vanderghast, Access MVP


toby131 said:
Thank you! I used the the WHERE clause version so that it was easy to go
back and add other fields and other administrators that do not use much
code
can see the design view. Is there a way to add another rolling average
for a
different time period to this same query or do I need to make a seperate
query using this same code and just changing the "2" to the appropriate
number for my new time period?

vanderghast said:
Assuming the field [Month] is a full date field, not a string, such as
2008.01.01 (supplying the first of the month as day part):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a INNER JOIN tableNameHere AS b
ON a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



Since it is a inner join, you can move the ON clause to the WHERE clause
(which makes the query editable in the graphical view):


SELECT a.[Month],
LAST(a.[%Incidents]),
LAST(a.Opportunities),
SUM(b.[%Incidents]) / SUM(b.Opportunities)

FROM tableNameHere AS a , tableNameHere AS b

WHERE a.[Month] >= b.[Month]
AND a.[Month] <= DateAdd( "m", 2, b.[Month] )

GROUP BY a.[Month]



In any cases, the magical number 2 comes from that we need to add 2
month,
to a date, to reach the third month first.


Vanderghast, Access MVP



toby131 said:
I have a query (FIR) with 4 fields: Month, Incidents, Opportunities,
and
%Incidents.
% Incidents is being calculated as Incidents/Opportunities. I want to
add
to this a three month average % Incidents. This would be the Sum of
Incidents for the listed month & previous 2 months / Sum of the
Opportunites
for the same time period. It is not critical to me that the first 2
months
in my data set show a meaningful number, since they will not have 2
months
before them. Example of what I am trying to produce. Thanks in
advance
for
any help with this calculation!

Month %Incidents Incidents Opportunities 3MonthAvg%
2008-01 17.05% 22 129 17.05%
2008-02 22.58% 28 124 22.58%
2008-03 22.12% 25 113 20.49%
2008-04 12.09% 11 91 19.51%
2008-05 10.64% 15 141 14.78%
2008-06 13.99% 20 143 12.27%
2008-07 14.55% 16 110 12.94%
2008-08 20.29% 28 138 16.37%
2008-09 15.83% 22 139 17.05%
2008-10 16.66% 20 120 17.63%
2008-11 13.93% 17 122 15.49%
2008-12 11.54% 12 104 14.16%
Click to expand...
Click to expand...
Click to expand...
 
Back
Top