If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rolling Average Calculation
I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and
%Incidents. % Incidents is being calculated as Incidents/Opportunities. I want to add to this a three month average % Incidents. This would be the Sum of Incidents for the listed month & previous 2 months / Sum of the Opportunites for the same time period. It is not critical to me that the first 2 months in my data set show a meaningful number, since they will not have 2 months before them. Example of what I am trying to produce. Thanks in advance for any help with this calculation! Month %Incidents Incidents Opportunities 3MonthAvg% 2008-01 17.05% 22 129 17.05% 2008-02 22.58% 28 124 22.58% 2008-03 22.12% 25 113 20.49% 2008-04 12.09% 11 91 19.51% 2008-05 10.64% 15 141 14.78% 2008-06 13.99% 20 143 12.27% 2008-07 14.55% 16 110 12.94% 2008-08 20.29% 28 138 16.37% 2008-09 15.83% 22 139 17.05% 2008-10 16.66% 20 120 17.63% 2008-11 13.93% 17 122 15.49% 2008-12 11.54% 12 104 14.16% |
#2
|
|||
|
|||
Rolling Average Calculation
Assuming the field [Month] is a full date field, not a string, such as
2008.01.01 (supplying the first of the month as day part): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a INNER JOIN tableNameHere AS b ON a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] Since it is a inner join, you can move the ON clause to the WHERE clause (which makes the query editable in the graphical view): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a , tableNameHere AS b WHERE a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] In any cases, the magical number 2 comes from that we need to add 2 month, to a date, to reach the third month first. Vanderghast, Access MVP "toby131" wrote in message ... I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and %Incidents. % Incidents is being calculated as Incidents/Opportunities. I want to add to this a three month average % Incidents. This would be the Sum of Incidents for the listed month & previous 2 months / Sum of the Opportunites for the same time period. It is not critical to me that the first 2 months in my data set show a meaningful number, since they will not have 2 months before them. Example of what I am trying to produce. Thanks in advance for any help with this calculation! Month %Incidents Incidents Opportunities 3MonthAvg% 2008-01 17.05% 22 129 17.05% 2008-02 22.58% 28 124 22.58% 2008-03 22.12% 25 113 20.49% 2008-04 12.09% 11 91 19.51% 2008-05 10.64% 15 141 14.78% 2008-06 13.99% 20 143 12.27% 2008-07 14.55% 16 110 12.94% 2008-08 20.29% 28 138 16.37% 2008-09 15.83% 22 139 17.05% 2008-10 16.66% 20 120 17.63% 2008-11 13.93% 17 122 15.49% 2008-12 11.54% 12 104 14.16% |
#3
|
|||
|
|||
Rolling Average Calculation
toby131 wrote:
I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and %Incidents. % Incidents is being calculated as Incidents/Opportunities. I want to add to this a three month average % Incidents. This would be the Sum of Incidents for the listed month & previous 2 months / Sum of the Opportunites for the same time period. It is not critical to me that the first 2 months in my data set show a meaningful number, since they will not have 2 months before them. Example of what I am trying to produce. Thanks in advance for any help with this calculation! Month %Incidents Incidents Opportunities 3MonthAvg% 2008-01 17.05% 22 129 17.05% 2008-02 22.58% 28 124 22.58% 2008-03 22.12% 25 113 20.49% 2008-04 12.09% 11 91 19.51% 2008-05 10.64% 15 141 14.78% 2008-06 13.99% 20 143 12.27% 2008-07 14.55% 16 110 12.94% 2008-08 20.29% 28 138 16.37% 2008-09 15.83% 22 139 17.05% 2008-10 16.66% 20 120 17.63% 2008-11 13.93% 17 122 15.49% 2008-12 11.54% 12 104 14.16% Try adding a calculated field that uses a subquery: RollAvg: (SELECT Sum(X.Incidents) / Sum(X.Opportunities) FROM table As X WHERE CDate(table.[Month]) Between DateDiff("m", -3, X.[Month]) And CDate)X.[Month]) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Rolling Average Calculation
Thank you! I used the the WHERE clause version so that it was easy to go
back and add other fields and other administrators that do not use much code can see the design view. Is there a way to add another rolling average for a different time period to this same query or do I need to make a seperate query using this same code and just changing the "2" to the appropriate number for my new time period? "vanderghast" wrote: Assuming the field [Month] is a full date field, not a string, such as 2008.01.01 (supplying the first of the month as day part): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a INNER JOIN tableNameHere AS b ON a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] Since it is a inner join, you can move the ON clause to the WHERE clause (which makes the query editable in the graphical view): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a , tableNameHere AS b WHERE a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] In any cases, the magical number 2 comes from that we need to add 2 month, to a date, to reach the third month first. Vanderghast, Access MVP "toby131" wrote in message ... I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and %Incidents. % Incidents is being calculated as Incidents/Opportunities. I want to add to this a three month average % Incidents. This would be the Sum of Incidents for the listed month & previous 2 months / Sum of the Opportunites for the same time period. It is not critical to me that the first 2 months in my data set show a meaningful number, since they will not have 2 months before them. Example of what I am trying to produce. Thanks in advance for any help with this calculation! Month %Incidents Incidents Opportunities 3MonthAvg% 2008-01 17.05% 22 129 17.05% 2008-02 22.58% 28 124 22.58% 2008-03 22.12% 25 113 20.49% 2008-04 12.09% 11 91 19.51% 2008-05 10.64% 15 141 14.78% 2008-06 13.99% 20 143 12.27% 2008-07 14.55% 16 110 12.94% 2008-08 20.29% 28 138 16.37% 2008-09 15.83% 22 139 17.05% 2008-10 16.66% 20 120 17.63% 2008-11 13.93% 17 122 15.49% 2008-12 11.54% 12 104 14.16% |
#5
|
|||
|
|||
Rolling Average Calculation
I assume this is possible, yes: add the table a third time, with another
alias, say, c, and add the condition: a.[Month] = c.[Month] AND a.[Month] = DateAdd("m" , 3, c.[Month] ) as example. I don't say that it will be fast, though. Vanderghast, Access MVP "toby131" wrote in message ... Thank you! I used the the WHERE clause version so that it was easy to go back and add other fields and other administrators that do not use much code can see the design view. Is there a way to add another rolling average for a different time period to this same query or do I need to make a seperate query using this same code and just changing the "2" to the appropriate number for my new time period? "vanderghast" wrote: Assuming the field [Month] is a full date field, not a string, such as 2008.01.01 (supplying the first of the month as day part): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a INNER JOIN tableNameHere AS b ON a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] Since it is a inner join, you can move the ON clause to the WHERE clause (which makes the query editable in the graphical view): SELECT a.[Month], LAST(a.[%Incidents]), LAST(a.Opportunities), SUM(b.[%Incidents]) / SUM(b.Opportunities) FROM tableNameHere AS a , tableNameHere AS b WHERE a.[Month] = b.[Month] AND a.[Month] = DateAdd( "m", 2, b.[Month] ) GROUP BY a.[Month] In any cases, the magical number 2 comes from that we need to add 2 month, to a date, to reach the third month first. Vanderghast, Access MVP "toby131" wrote in message ... I have a query (FIR) with 4 fields: Month, Incidents, Opportunities, and %Incidents. % Incidents is being calculated as Incidents/Opportunities. I want to add to this a three month average % Incidents. This would be the Sum of Incidents for the listed month & previous 2 months / Sum of the Opportunites for the same time period. It is not critical to me that the first 2 months in my data set show a meaningful number, since they will not have 2 months before them. Example of what I am trying to produce. Thanks in advance for any help with this calculation! Month %Incidents Incidents Opportunities 3MonthAvg% 2008-01 17.05% 22 129 17.05% 2008-02 22.58% 28 124 22.58% 2008-03 22.12% 25 113 20.49% 2008-04 12.09% 11 91 19.51% 2008-05 10.64% 15 141 14.78% 2008-06 13.99% 20 143 12.27% 2008-07 14.55% 16 110 12.94% 2008-08 20.29% 28 138 16.37% 2008-09 15.83% 22 139 17.05% 2008-10 16.66% 20 120 17.63% 2008-11 13.93% 17 122 15.49% 2008-12 11.54% 12 104 14.16% |
Thread Tools | |
Display Modes | |
|
|