Solved

Calculate 12 month rolling average

Posted on 2004-10-26
2,162 Views
Last Modified: 2011-09-20
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%

I can pull the last twelve months but need to calculate the rolling average - the above is the example from excel
0
Question by:tupalee
    31 Comments
     
    LVL 77

    Expert Comment

    by:peter57r
    Hello tupalee,

    You need to describe your table(s).

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    Hi Pete,

    Describe table(s) how?
    tupalee
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Description of Fields, plus datatypes and sample data.

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    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                                  
     
    Date       Num           Num           Double      Num           Num        Double     Double        Double

    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    


    0
     
    LVL 1

    Author Comment

    by:tupalee
    thank you for helping me
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    This looks a lot easier in Excel.

    Why do you want to do it in Access?

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    I am making an intranet in ASP.Net and the data is spread out in excel throughout numerous excel sheets (of which can't be changed)and I wanted to localize the data so that I can pull it in the format I want it from one place
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    I take it the data you are showing is still held in Excel worksheets.  You will appreciate that Access requires each column to have a unique name.  So this is not a good starting point for importing or linking.

    If this issue could be overcome then it might be possible to write a function in Access which does the calculation.
    What would you want to happen if there are less than 12 entries - do you want to calulate the average based on the number of entries or do you want to ignore that month?

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    the key issue I already worked out

    the month issue - the rolling average would start from the initial month
    Example: from above the month rolling would start would be Feb 04 (Data1 = 0 and Data2 = 0 which makes Avg = 0%)

    Thanks for brainstorming with me

    Tuppence
    0
     
    LVL 77

    Accepted Solution

    by:
    I have defined a table  - 'mydata' -like this:
    MonthX - Date/Time
    Data1 -number
    Data2 -number
    DataAvg -text
    Data1a
    Data2a
    DataAvga
    MthAvg


    Then I have created a query :
    SELECT mydata.Monthx, mydata.Data1, mydata.Data2, mydata.DATA1a, mydata.DATA2a, getravg([monthx]) AS Roll
    FROM mydata;

    This gives the results as below:


    Monthx               Data1         Data2      DATA1a       DATA2a      Roll
    01/02/2004                  5      5      100
    01/03/2004      0      1      5      6      83.33
    01/04/2004      2      5      11      11      82.14
    01/05/2004      1      3      12      14      80
    01/06/2004      3      6      12      13      79.68
    01/07/2004      3      6      6      8      76.92
    01/08/2004      5      8      9      9      77.89
    01/09/2004      5      5      11      12      80.35

    This is the function I have used.
    it does   (data1+data1a)/(data2+data2a).
    I believe it will go back up to 12 months if there is sufifcient data


    Function getRavg(pMonth)
    Dim sd1, sd2, sd1a, sd2a
    Dim sDate
    Dim eMonth
    sDate = Format(DateAdd("yyyy", -1, pMonth), "yyyy-mm-dd")
    eMonth = Format(pMonth, "yyyy-mm-dd")

    sd1 = DSum("nz(Data1)", "mydata", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
    sd2 = DSum("nz(Data2)", "mydata", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
    sd1a = DSum("nz(Data1a)", "mydata", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
    sd2a = DSum("nz(Data2a)", "mydata", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")

    If (sd2 + sd2a) <> 0 Then
        getRavg = (sd1 + sd1a) / (sd2 + sd2a)
    Else
        getRavg = 0
    End If
    getRavg = Int(getRavg * 10000) / 100
    End Function


    Hope this helps

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    Thank you soo much - to clarify

    OK - made a module(getRavg) with the function in it (Function getRavg(PPAP))

    My table'PPAP-Complaints'                 - Your Table'mydata'
    PPAP                                                MonthX - Date/Time
    New Product On-time                        Data1 -number
    New Product Total                                Data2 -number
                                                           DataAvg -text
     Recert On-time                             Data1a
     Recert Total                                   Data2a
                                                            DataAvga
                                                               MthAvg

    and the function would look like this.......

    Function getRavg(PPAP)
    Dim sd1, sd2, sd1a, sd2a
    Dim sDate
    Dim eMonth
    sDate = Format(DateAdd("yyyy", -1, pMonth), "yyyy-mm-dd")
    eMonth = Format(pMonth, "yyyy-mm-dd")

    sd1 = DSum("nz(New Product On-time)", "PPAP-Complaints", "PPAP >#" & sDate & "# and PPAP<=#" & eMonth & "#")
    sd2 = DSum("nz(New Product Total)", "PPAP-Complaints", "PPAP >#" & sDate & "# and PPAP<=#" & eMonth & "#")
    sd1a = DSum("nz(Recertification On-time)", "PPAP-Complaints", "PPAP >#" & sDate & "# and PPAP<=#" & eMonth & "#")
    sd2a = DSum("nz(Recertification Total)", "PPAP-Complaints", "PPAP >#" & sDate & "# and PPAP<=#" & eMonth & "#")

    If (sd2 + sd2a) <> 0 Then
        getRavg = (sd1 + sd1a) / (sd2 + sd2a)
    Else
        getRavg = 0
    End If
    getRavg = Int(getRavg * 10000) / 100
    End Function


    Then to call the  function?   Rolling: getRavg([PAPP]) in the query design view - I get a uindefined function getRavg in expression...

    what am I doing wrong
    0
     
    LVL 1

    Author Comment

    by:tupalee
    I found this web page and at the end it talks about putting it in a query but I can't seem to get mine working
    0
     
    LVL 1

    Author Comment

    by:tupalee
    oops - it helps to have the site
    http://www.fontstuff.com/vba/vbatut04.htm

    thx again
    0
     
    LVL 1

    Author Comment

    by:tupalee
    ok - got it to run but am getting a runtime 3075 error
    that there is some  syntax error (missing operator)  in query expression 'Sum(nz(New Product On-time))'
    0
     
    LVL 1

    Author Comment

    by:tupalee
    You truley have the answer - and I see the logic but I can't get it to function on mine
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Put [ ] around the New Product On-time (because there are spaces in the fieldname)


    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    yeah tried that too - but for some reason it didn't work yesterday and does today.

    I really appreciate everything - one last question

    If I do this to more than one table can I send a month, data column, and table name via argument....

    IE - Monthx = pMonth........getRavg([Monthx])
     can I don this.................getRAvg([Monthx],[Data1],[mydata]) and have it fill......getRavg(pMonth,DataColumn, TableName)

    I really appreciate the fact that you take the time to help newbie's like me
    0
     
    LVL 1

    Author Comment

    by:tupalee
    in other words - I have more than one table to accomplish this on
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Are you saying you would want to pass the field names and table name into the function - so that id uses the field name  you have passed rather than data1 for example?

    If so, I'm sure you can but I 'll need a minute or two to work out the correct syntax.

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    correct - I have about 15 table to to this on....

    two with the two(double column) fields like we did
    three with avg just one field to avg (not rolling just current avg for last twelve)
    the rest one (double column) field

    so I am trying to work out to just pass the name and the function converts it to its own name - just like VB so that I don't have to rewrite the function for each table

    Thank you again -
    I don't completely understand the point system yet - so would you like me to start a new question for more points on this issue of transferring the names to a function

    Tuppence
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Here is the modied function.

    Function getRavg(pMonth, pDatafield1, pdatafield2, pdatafield3, pdatafield4, ptablename)
    ' note that the routine will sum the sums of pDatafield1 and pdatafield3
    'and divide by the sum of the sums of pdatafield2 and pdatafield4

    Dim sd1, sd2, sd1a, sd2a
    Dim sDate
    Dim eMonth
    sDate = Format(DateAdd("yyyy", -1, pMonth), "yyyy-mm-dd")
    eMonth = Format(pMonth, "yyyy-mm-dd")

     sd1 = DSum("nz([" & pDatafield1 & "])", "[" & ptablename & "]", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
     sd2 = DSum("nz([" & pdatafield2 & "])", "[" & ptablename & "]", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
    sd1a = DSum("nz([" & pdatafield3 & "])", "[" & ptablename & "]", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")
    sd2a = DSum("nz([" & pdatafield4 & "])", "[" & ptablename & "]", "Monthx >#" & sDate & "# and monthx<=#" & eMonth & "#")

    If (sd2 + sd2a) <> 0 Then
        getRavg = (sd1 + sd1a) / (sd2 + sd2a)
    Else
        getRavg = 0
    End If
    getRavg = Int(getRavg * 10000) / 100
    End Function


    Example:
    getRavg(MonthX, "data1","Data2","Data1a","Data2a","mytable")

    Note that all names must be in quotes.

    Pete

    0
     
    LVL 1

    Author Comment

    by:tupalee
    if I send the folling I get a type mismatch at sDate

    RollingPPAP: getRavg([PPAP],[New Product On-time],[New Product Total],[Recertification On-time],[Recertification Total],[PPAP-Complaints])

    should I not have the [] or put "" around them



    0
     
    LVL 77

    Expert Comment

    by:peter57r
    RollingPPAP: getRavg([PPAP],[New Product On-time],[New Product Total],[Recertification On-time],[Recertification Total],[PPAP-Complaints])

    This passes the  data held in one record to the function.  That is not what you want.  You simply want to tell the function which fieldname to use.  Fieldnames must be passed as strings.
    So you want:
    RollingPPAP: getRavg([PPAP],"New Product On-time","New Product Total","Recertification On-time","Recertification Total","PPAP-Complaints")

    Pete


    0
     
    LVL 1

    Author Comment

    by:tupalee
    did that and changed the Monthx in the function to pMonth and getting a runtime error 2001 saying I cancelled the previous operation and debug sends me to the sd1 line
    0
     
    LVL 1

    Author Comment

    by:tupalee
    should I treat the pMonth like the datafields as a string and put " & pMonth & " around it in the function
    0
     
    LVL 1

    Author Comment

    by:tupalee
    I changed it to the following and I get 80.35 (which is Sept's) for all the months

     sd1 = DSum("nz([" & pdatafield1 & "])", "[" & ptablename & "]", "#" & pMonth & "#>#" & sDate & "# and #" & pMonth & "# <=#" & eMonth & "#")
     sd2 = DSum("nz([" & pdatafield2 & "])", "[" & ptablename & "]", "#" & pMonth & "#>#" & sDate & "# and #" & pMonth & "# <=#" & eMonth & "#")
    sd1a = DSum("nz([" & pdatafield3 & "])", "[" & ptablename & "]", "#" & pMonth & "#>#" & sDate & "# and #" & pMonth & "# <=#" & eMonth & "#")
    sd2a = DSum("nz([" & pdatafield4 & "])", "[" & ptablename & "]", "#" & pMonth & "#>#" & sDate & "# and #" & pMonth & "# <=#" & eMonth & "#")


    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Trying to make the Month field name a variable is much more complicated (I'm tempted to say impossible but that is seldom true) .
     The month value is the record key itself so just passing the field name is no good because the function wouldn't know which value of that field to take. ( so I guess it takes the first one which is why all your answers are the same).

    You have to give the function a reference to a a specific date - the function can't work it out.

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    I put in the name - ie PPAP (where the month os located) in place of Monthx and it worked  - so I am trying to pass that

    RollingPPAP: getRavg([PPAP],"PPAP","New Product On-time","New Product Total","Recertification On-time","Recertification Total","PPAP-Complaints")

    "[" & monthx & "] >#" & sDate & "# and [" & monthx & "]<=#" & eMonth & "#")


    and it worked!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Thank you so much I would shake your hand and give you a hug if I could
    0
     
    LVL 1

    Author Comment

    by:tupalee
    now I just have to modify it for a single (two column) data table
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    OK
    Sounds like you are in control now.

    I'm out of my office now for the rest of this week so I won't be able to post again before then.

    Pete
    0
     
    LVL 1

    Author Comment

    by:tupalee
    I appreciate everything - I will definitely let you know the outcome
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    860 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

    14 Experts available now in Live!

    Get 1:1 Help Now