Solved

# Calculate a 12 month rolling average

Posted on 2004-10-26
992 Views
I have a db that has a 12 month rolling that needs to be calculated on a db and then will be displayed.
I understand how to do this in excel easily but I need to display each rolling 12 for the last 12 months as well.
Example:
Current      Sep-04      Aug-04      Jul-04      Jun-04
14,599        1,405        1,169        1,018        1,338
15,519        1,507        1,267        1,094        1,432
% by Month            93.2%      92.3%      93.1%      93.4%
12 Month Rolling      94.1%      94.1%      94.2%      94.4%      94.7%

0
Question by:tupalee

LVL 48

Expert Comment

You can retrieve dates from the last 12 months by filtering with DateDiff():

.. WHERE DateDiff("m", [YourDateField], Date()) <=12

Then use this in a crosstab query where you use month for column.

/gustav
0

LVL 1

Author Comment

I can also sort descending through the months and then pull the top 12

The above was an example from excel and I would like to do the same thing in access
0

LVL 1

Author Comment

The rolling 12 calculation is what I am having issues with
0

LVL 48

Expert Comment

I see.
I guess you would need to create a series of queries and then assemble the results from these with a union query.

Or you could use subqueries to retrieve the averages - as shown here:

http://www.dbforums.com/archive/index.php/t-814418.html

It's hard to be more specific with no details on table structure.

/gustav
0

LVL 1

Author Comment

that's what I was thinking (about the sub queries)

Month     Data1        Data2         DataAvg     DATA1      DATA2      DATAAvg   MthAvg     Rolling 12Avg
Sep-04      5      5      100.00%      11      12      91.67%      94.12%
Aug-04      5      8      62.50%      9      9      100.00%      82.35%
Jul-04      3      6      50.00%      6      8      75.00%      64.29%
Jun-04      3      6      50.00%      12      13      92.31%      78.95%
May-04      1      3      33.33%      12      14      85.71%      76.47%
Apr-04      2      5      40.00%      11      11      100.00%      81.25%
Mar-04      0      1      0.00%      5      6      83.33%      71.43%
Feb-04                        5      5      100.00%
Jan-04
Dec-03
Nov-03
Oct-03

So the thought process is to take Sum(Data1 + DATA1) for last 12 and then divide by Sum(Data2+DATA2) for last 12 and do this for each month.

FYI: some of this data as seen above is fresh and therefore doesn't extned back 12 months
0

LVL 1

Author Comment

Month     Data1        Data2         DataAvg     DATA1      DATA2      DATAAvg   MthAvg     Rolling 12Avg
Sep-04       5             5                100.00%     11             12         91.67%     94.12%
Aug-04     5               8                   62.50%     9              9         100.00%     82.35%
Jul-04       3               6                50.00%         6             8            75.00%     64.29%
Jun-04     3                6                50.00%      12               13         92.31%     78.95%
May-04     1               3                 33.33%      12             14         85.71%     76.47%
Apr-04     2                5                40.00%      11             11         100.00%     81.25%
Mar-04     0                1                  0.00%      5              6          83.33%     71.43%
Feb-04                                                          5              5           100.00%
Jan-04
Dec-03
Nov-03
Oct-03

Sorry this probably make more since
0

LVL 48

Expert Comment

That could be something like this:

Month      DataA1      DataA2      DataAAvg      DataB1      DataB2      DataBAvg      MthAvg      Roll12Avg
01-09-2004      5      5      100,00%      11      12      91,67%      94,12%      78,41%
01-08-2004      5      8      62,50%      9      9      100,00%      82,35%      75,79%
01-07-2004      3      6      50,00%      6      8      75,00%      64,29%      74,48%
01-06-2004      3      6      50,00%      12      13      92,31%      78,95%      77,02%
01-05-2004      1      3      33,33%      12      14      85,71%      76,47%      76,38%
01-04-2004      2      5      40,00%      11      11      100,00%      81,25%      76,34%
01-03-2004      0      1      0,00%      5      6      83,33%      71,43%      71,43%
01-02-2004                        5      5      100,00%
01-01-2004
01-12-2003
01-11-2003
01-10-2003

from:

SELECT
Month,
DataA1,
DataA2,
[DataA1]/[DataA2] AS DataAAvg,
DataB1,
DataB2,
[DataB1]/[DataB2] AS DataBAvg,
([DataA1]+[DataB1])/([DataA2]+[DataB2]) AS MthAvg,
(SELECT
AVG(([DataA1]+[DataB1])/([DataA2]+[DataB2]))
FROM
tblStats As T
WHERE
DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 12) AS Roll12Avg
FROM
tblStats
GROUP BY
Month,
DataA1,
DataA2,
DataB1,
DataB2
ORDER BY
Month DESC;

Maybe this:

DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 12) AS Roll12Avg

should be:

DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 11) AS Roll12Avg

/gustav
0

LVL 1

Author Comment

i am getting a syntax error and can't figure out where

SELECT TOP 12 [PPAP-Complaints].PPAP,
[PPAP-Complaints].[New Product On-time],
[PPAP-Complaints].[New Product Total],
[New Product On-time]/[New Product Total] AS NewProductPercentage,
[PPAP-Complaints].[Recertification On-time],
[PPAP-Complaints].[Recertification Total],
[Recertification On-time]/[Recertification Total] AS [Recertification Percentage],
([New Product On-time]+[Recertification On-time])/([New Product Total]+[Recertification Total]) AS [Percentage On-Time],

(SELECT AVG(([New Product On-Time]+[Recertification On-time])/([New Product Total]+[Recertification Total])) FROM [PPAP-Complaints] as Rolling WHERE DateDiff('m', [PPAP-Complaints].PPAP, Date()) <=12 as Rolling12Avg)

FROM [PPAP-Complaints]
ORDER BY [PPAP-Complaints].PPAP DESC;
0

LVL 1

Author Comment

thank you for tryng to help me
0

LVL 48

Accepted Solution

.. [PPAP-Complaints] as Rolling WHERE DateDiff('m', [PPAP-Complaints].PPAP, Date()) <=12) as Rolling12Avg

/gustav
0

## Featured Post

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…