Solved

Calculate a 12 month rolling average

Posted on 2004-10-26
992 Views
Last Modified: 2008-01-09
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
    10 Comments
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    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

    by:tupalee
    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

    by:tupalee
    The rolling 12 calculation is what I am having issues with
    0
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    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

    by:tupalee
    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

    by:tupalee
    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

    by:Gustav Brock
    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

    by:tupalee

    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

    by:tupalee
    thank you for tryng to help me
    0
     
    LVL 48

    Accepted Solution

    by:
    This line should read:

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

    /gustav
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    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…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now