0 Replies Latest reply on Mar 4, 2014 3:58 AM by Scott Morton

    How can I link idenical matters in snap shot data?

    Scott Morton

      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])

        then 1

      else 0




      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.



      can anyone help?