tupalee
asked on
Calculate 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%
I can pull the last twelve months but need to calculate the rolling average - the above is the example from excel
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
ASKER
Hi Pete,
Describe table(s) how?
tupalee
Describe table(s) how?
tupalee
Description of Fields, plus datatypes and sample data.
Pete
Pete
ASKER
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
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
ASKER
thank you for helping me
This looks a lot easier in Excel.
Why do you want to do it in Access?
Pete
Why do you want to do it in Access?
Pete
ASKER
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
ASKER
ASKER
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))'
that there is some syntax error (missing operator) in query expression 'Sum(nz(New Product On-time))'
ASKER
You truley have the answer - and I see the logic but I can't get it to function on mine
Put [ ] around the New Product On-time (because there are spaces in the fieldname)
Pete
Pete
ASKER
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([Mon thx])
can I don this.................getRA vg([Monthx ],[Data1], [mydata]) and have it fill......getRavg(pMonth,D ataColumn, TableName)
I really appreciate the fact that you take the time to help newbie's like me
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([Mon
can I don this.................getRA
I really appreciate the fact that you take the time to help newbie's like me
ASKER
in other words - I have more than one table to accomplish this on
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
If so, I'm sure you can but I 'll need a minute or two to work out the correct syntax.
Pete
ASKER
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
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
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","m ytable")
Note that all names must be in quotes.
Pete
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","
Note that all names must be in quotes.
Pete
ASKER
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
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
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
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
ASKER
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
ASKER
should I treat the pMonth like the datafields as a string and put " & pMonth & " around it in the function
ASKER
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 & "#")
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 & "#")
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
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
ASKER
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
RollingPPAP: getRavg([PPAP],"PPAP","New
"[" & 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
ASKER
now I just have to modify it for a single (two column) data table
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
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
ASKER
I appreciate everything - I will definitely let you know the outcome
You need to describe your table(s).
Pete