I have data sorted by ascending GROUP INFORCE and LAG, as shown below, and I'm interested in calculating a kind of "5 period rolling average" for Cum_Lag_Incrs. The way I need to compute my rolling average is by removing the Max and Min relevant observation and dividing by 3 (e.g. the "average" of the set {1,2,3,4,5} would be 3).
In addition to the average being based on 3 central observations of 5, it needs to come from the correct subset of data.
To be specific, for example: For GROUP=AA and LAG=1, Roll_Avg will be based only on the previous 5 GROUP=AA and LAG=1 values of Cum_Lag_Incrs; for GROUP=AB and LAG=2, Roll_Avg will be based only on the previous 5 GROUP=AB and LAG=2 values of Cum_Lag_Incrs; etc.
Note also that in data below I've shown Roll_Avg as either blank or "Calculate": I've done this because if there aren't 5 previous month of data available, then no calculation should be made.
Group
INFORCE
Lag
Cum_Lag_Incrs
Roll_Avg
AA
201301
0
AA
201301
1
3.038434616
AA
201301
2
1.13597727
AA
201301
3
1.088584718
AA
201302
0
AA
201302
1
3.322640299
AA
201302
2
1.182485247
AA
201302
3
1.036479476
AA
201303
0
AA
201303
1
3.413788991
AA
201303
2
1.120095681
AA
201303
3
1.047491904
AA
201304
0
AA
201304
1
2.696971256
AA
201304
2
1.110429149
AA
201304
3
1.046630304
AA
201305
0
AA
201305
1
2.700299318
AA
201305
2
1.111799418
AA
201305
3
1.045353415
AA
201306
0
Calculate
AA
201306
1
3.156808222
Calculate
AA
201306
2
1.213467751
Calculate
AA
201306
3
1.152644537
Calculate
AA
201307
0
Calculate
AA
201307
1
3.149903686
Calculate
AA
201307
2
1.236895962
Calculate
AA
201307
3
1.038549449
Calculate
AA
201308
0
Calculate
AA
201308
1
3.388799982
Calculate
AA
201308
2
1.17331458
Calculate
AA
201308
3
1.037821673
Calculate
AA
201309
0
Calculate
AA
201309
1
2.223705515
Calculate
AA
201309
2
1.107696717
Calculate
AA
201309
3
1.067502797
Calculate
AA
201310
0
Calculate
AA
201310
1
2.598374951
Calculate
AA
201310
2
1.183557366
Calculate
AA
201310
3
1.068765806
Calculate
AA
201311
0
Calculate
AA
201311
1
2.684124925
Calculate
AA
201311
2
1.882073772
Calculate
AA
201311
3
1.639452593
Calculate
AB
201301
0
AB
201301
1
3.034393497
AB
201301
2
1.13446642
AB
201301
3
1.087136901
AB
201302
0
AB
201302
1
3.326727146
AB
201302
2
1.183939704
AB
201302
3
1.035100958
AB
201303
0
AB
201303
1
3.409248651
AB
201303
2
1.121473399
AB
201303
3
1.04609874
AB
201304
0
AB
201304
1
2.693384284
AB
201304
2
1.108952278
AB
201304
3
1.047917659
AB
201305
0
AB
201305
1
2.703620687
AB
201305
2
1.113166932
AB
201305
3
1.046639199
AB
201306
0
Calculate
AB
201306
1
3.160691097
Calculate
AB
201306
2
1.214960317
Calculate
AB
201306
3
1.15111152
Calculate
AB
201307
0
Calculate
AB
201307
1
3.145714314
Calculate
AB
201307
2
1.238417344
Calculate
AB
201307
3
1.037168178
Calculate
AB
201308
0
Calculate
AB
201308
1
3.384292878
Calculate
AB
201308
2
1.171754072
Calculate
AB
201308
3
1.039098194
Calculate
AB
201309
0
Calculate
AB
201309
1
2.226440673
Calculate
AB
201309
2
1.106223481
Calculate
AB
201309
3
1.068815825
Calculate
AB
201310
0
Calculate
AB
201310
1
2.601570952
Calculate
AB
201310
2
1.181983235
Calculate
AB
201310
3
1.070080388
Calculate
AB
201311
0
Calculate
AB
201311
1
2.680555039
Calculate
AB
201311
2
1.879570614
Calculate
AB
201311
3
1.64146912
Calculate
For clarification, note the following: Cum_Lag_Incrs is the ratio between a cumulative total (not shown here) in Lag N+1 and Lag N where N=0,1,2,3. So Cum_Lag_Incrs is always 0 for LAG=0 since there is no cumulative total to speak of before LAG 0. It's like if you sold a guy a car on a payment plan and you tracked the ratio between cumulative payments in this month and cumulative payments in last month; in the base month (the month you sold the car) there would be no cumulative total for this guy through the previous month.
Also, as an example, here's how I'd calculate the Roll_Avg for GROUP=AA, INFORCE=201306, Lag=0,1,2,3. Note that the previous 5 relevant data points are the previous 5 relevant data points from which the Roll_Avg will be calculated (e.g. for GROUP=AA, INFORCE=201306, Lag=1, the relevant values are Cum_Lag_Incrs from 201301-201305).
Group
INFORCE
Lag
Cum_Lag_Incrs
Roll_Avg
Previous 5 Relevant Data Points
AA
201306
0
0
0
0
0
0
0
AA
201306
1
3.156808222
3.020458078
3.038434616
3.322640299
3.413788991
2.696971256
2.700299318
AA
201306
2
1.213467751
=(1.13597727+1.182485247+1.120095681+1.110429149+1.111799418-1.110429149-1.182485247)/3
1.13597727
1.182485247
1.120095681
1.110429149
1.111799418
AA
201306
3
1.152644537
1.046491874
1.088584718
1.036479476
1.047491904
1.046630304
1.045353415
... View more