I am dealing with data based on claims brought against an insurer.
This is snap shot data taken towards the end of the month over the last 24 months. Therefore the same claim may appear 24 times at different stages of development if it was open right at the beginning of the contract.
I am trying to show how many claims have closed in each month as a % of the live claims which were open at that point.
In order to identify the claims closed in the snap shot month I have used the formula
IF Month([Closed Date]) = Month([Date of Snap Shot]) and
Year([Closed Date]) = Year([Date of Snap Shot])
However, the problem is that although some claims may closed in a one month, they are not reported until the next month so are counted at 0 the whole way through. Also the snap shot date and often a week before the end of the month which doesn’t help.
What I need is to some how populate the data rows with the closure date as in the last snap shot so need to link previous views of a claim with the most recent. I could blend the data with another data extract containing just the most recent month's data but I also need this to be automated.
Ive attached the workbook to illustrate what I mean.