Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculate a 12 month rolling average

Posted on 2004-10-26
10
Medium Priority
?
1,029 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
Comment
Question by:tupalee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12410557
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
ID: 12410817
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
ID: 12410918
The rolling 12 calculation is what I am having issues with
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12411125
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
ID: 12411289
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
ID: 12411336
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 52

Expert Comment

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

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
ID: 12413017
thank you for tryng to help me
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 12413151
This line should read:

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

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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