Building 12 month rolling periods in the past

[csh8428]

EDIT: I redid the formulas in sample sheet that provided new numbers. 

I have a dataset where I need to sum the number of [Instances] for 12 periods of the 12 prior rolling months for each period.

Each period would sum the [Instances] being grouped by [KEY_c], [CUST_TYPE_C], [DEACT_CHANGE_REAS_CD], [DEACT_CHANGE_REAS_GRP_c].

Period 12 would be based off the max [EXP_DT] and sum the instances going 12 months back. Period 11: would begin 1 month prior to period 12, so on and so forth.

I’ve tried various methods of tiling, multi-row formulas, generating rows, but just can’t get it.

The max [EFF_DT] will be different for all records so I can’t go off of “today” and not all months will have an [Instance] value for a given grouping.

I’ve attached a sample spreadsheet that contains the sample dataset as well as the expected results.

Thanks for any help!!

-Craig

[binuacs]

@csh8428 can you explain how did you get the below instances based on your input file, 

[csh8428]

@binuacs It's kind of hard to explain in text. But each 12 month rolling period starts with the maximum [EFF_DT] for the given aggregation and looks sums the [INSTANCES] for the prior 12 months from that [EFF_Dt]. The data will only have at maximum 2 years of data, so there will always only be 12 rolling periods of actual data. After redoing it with the formulas I see some of the numbers changed. 

I've attached a new spreadsheet with a "Data with Results Explanation" tab. I added dummy records for every month with [INSTANCES] value of 0 to highlight the mechanics. In reality the data will not have every month. It will only have some months, or even 1 month as you see on the "Data" tab.

You can see the mechanics of the rolling periods as excel formulas in column J.

[binuacs]

@csh8428 your expected result was not matching the result given in the "Data with Result Explanation" tab, that was the confusing part 

[csh8428]

@binuacs this is very close!, but the results aren't matching the anticipated results. Sort it by [Key_c],[CUST_TYPE_c],[DEACT_CHANGE_REASON_CD]. [ROLLING_TWELVE_MONTH_PERIOD] DESCENDING. . Compare that to the anticipated results and you'll see what I'm referring to.

[binuacs]

@csh8428 updated the workflow