[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

Calculate a 12 month rolling average

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
tupalee
Asked:
tupalee
  • 6
  • 4
1 Solution
 
Gustav BrockCIOCommented:
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
 
tupaleeAuthor Commented:
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
 
tupaleeAuthor Commented:
The rolling 12 calculation is what I am having issues with
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Gustav BrockCIOCommented:
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
 
tupaleeAuthor Commented:
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
 
tupaleeAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
tupaleeAuthor Commented:

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
 
tupaleeAuthor Commented:
thank you for tryng to help me
0
 
Gustav BrockCIOCommented:
This line should read:

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

/gustav
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now