Rolling Window

  • Thread starter Thread starter Doc
  • Start date Start date
D

Doc

I need a 7 day rolling window average for each date shown on a query, for
example:

Date range 10/1/2008 - 10/31/2008

Fields:
Daily Average
MTD Average
Previous Month Average
12 Month Average
7 day rolling window average


Explination:
Daily average comes from the primary table, just the total line average
MTD avg comes from a second query, linked in
PrevMo avg comes form a second query, linked in
12Mo avg comes from a second query, linked in

Problem:
The 7 day average is supposed to show the last 7 days average for each date.
For example, if the report was ran on 10/23/2008, a single line for every day
for the month of october would be shown, Daily average would flucuate for
each day, the mtd, prevmo, and 12mo averages would all be the same, but the 7
day rolling window should show the average as such; so that the line for the
23rd, would show an average for 10/17-10/23, likewise the line for the 22nd,
would show an average for 10/16-10/22.


I have accomplished this task with a simple program shown below, but the
query takes a couple of hours to run, I need it in a couple of minutes, max.

Any help would be AWESOME! Thanks!



Function Scrap7DayAverage(ByVal IDMachine As Integer, ByVal Dt As Date) As
Single
On Error GoTo ErrHand
Dim ScrapPerc(7) As Single, I As Integer

For I = 0 To 6
If ECount("*", "qryScrapPercDailyCurMo", "IDMachine = " & IDMachine
& " And Date = #" & Dt - I & "#") = 0 Then
ScrapPerc(I) = 0
Else
ScrapPerc(I) = ELookUp("ScrapPerc", "qryScrapPercDailyCurMo",
"IDMachine = " & IDMachine & " And Date = #" & Dt - I & "#")
End If
Next I

ScrapPerc(7) = ScrapPerc(0) + ScrapPerc(1) + ScrapPerc(2) + ScrapPerc(3)
+ ScrapPerc(4) + ScrapPerc(5) + ScrapPerc(6)
ScrapPerc(7) = ScrapPerc(7) / 7

Scrap7DayAverage = ScrapPerc(7)

ExitHand:
For I = 0 To 7
ScrapPerc(I) = 0
Next I
I = 0
IDMachine = 0
Dt = 0
Exit Function

ErrHand:
Echo True
DoCmd.SetWarnings True
eNumb = Err.Number
eDesc = Err.Description
ErrorLog "Production", "Scrap7DayAverage", "ePr-305", ActiveFrm, eNumb,
eDesc
eNumb = 0
eDesc = ""
GoTo ExitHand
End Function
 
A rolling seven day average could be calculated in a query

SELECT A.TheDate, Avg(B.Quantity) as RollingAverage
FROM YourTable as A INNER JOIN YourTable as B
ON A.TheDate >= B.TheDate-6 and A.TheDate <= B.TheDate



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Forgot the group by clause

SELECT A.TheDate, Avg(B.Quantity) as RollingAverage
FROM YourTable as A INNER JOIN YourTable as B
ON A.TheDate >= B.TheDate-6 and A.TheDate <= B.TheDate
GROUP BY A.TheDate

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you for the response!

I'm slightly confused, so I'm hoping if I tell you my table names, you can
hook me up with a solution:

tblProduction 'Main data table
tblDates 'List of dates from 1/1/2007 - 12/31/2009
qryActiveMachines 'Query of active machines
qryDatesCurMo 'Creates list of machines for every date (for example, Machine
1 will have a line for every date beginning 1/1/2007 through 12/31/2009)
qryScrapPercDailyCurMo 'Sums production data to create daily scrap
percentage for each machine, and each date. If no data was entered, shows a
0% scrap rate
qryScrapPercMTD 'Sums production data to create MTD average scrap rate
qryScrapPerc12Mo 'Sums production data to create 12 Month scrap rate
qryScrapPercPrevMo 'Sums production data for previous month

All queries are brought together in qryChartData, here's the SQL

SELECT qryDatesCurMo.IDMachine, qryDatesCurMo.Machine, qryDatesCurMo.Date,
Format([qryDatesCurMo].[Date],"d") AS [Day],
IIf(IsNull([qryScrapPercDailyCurMo].[ScrapPerc]),0,[qryScrapPercDailyCurMo].[ScrapPerc])
AS DailyScrap,
IIf(IsNull([qryScrapPercMTD].[ScrapPerc]),0,[qryScrapPercMTD].[ScrapPerc]) AS
MTDScrap, IIf(IsNull([qryScrapGoalCur].[Goal]),0,[qryScrapGoalCur].[Goal]) AS
ScrapGoal,
IIf(IsNull([qryScrapPercPrevMo].[ScrapPerc]),0,[qryScrapPercPrevMo].[ScrapPerc])
AS ScrapPrevMo,
IIf(IsNull([qryScrapPerc12Mo].[ScrapPerc]),0,[qryScrapPerc12Mo].[ScrapPerc])
AS Scrap12Mo
FROM ((((qryDatesCurMo LEFT JOIN qryScrapPercDailyCurMo ON
(qryDatesCurMo.IDMachine = qryScrapPercDailyCurMo.IDMachine) AND
(qryDatesCurMo.Date = qryScrapPercDailyCurMo.Date)) LEFT JOIN qryScrapPercMTD
ON qryDatesCurMo.IDMachine = qryScrapPercMTD.IDMachine) LEFT JOIN
qryScrapGoalCur ON qryDatesCurMo.IDMachine = qryScrapGoalCur.IDMachine) LEFT
JOIN qryScrapPercPrevMo ON qryDatesCurMo.IDMachine =
qryScrapPercPrevMo.IDMachine) LEFT JOIN qryScrapPerc12Mo ON
qryDatesCurMo.IDMachine = qryScrapPerc12Mo.IDMachine
ORDER BY qryDatesCurMo.Date, qryDatesCurMo.Machine;

So what I need added to this, is a 7 day rolling window for each machine and
date of the data in qryScrapPerCailyCurMo. Mean while, I'm going to be
trying to figure out the 'RollingAverage' bit.

Thanks again!
 
Back
Top