2 Replies Latest reply: Dec 8, 2017 3:09 PM by Ruben Marin RSS

    Set Analysis with Dates

    Chris Weldon

      Hope someone can help - I am a little stumped!


      I have a series of records that have a ClaimID as the key.  The records are populated every month, and previous claims are recaptured.  Therefore ClaimID 1234 could be in multiple months data.


      So for most things I only want to look at the Claims as they were in that month, so I apply a filter using the DataAsAt field at the app level.


      But in one sheet I want to build up a table of movements by month.  So I need a column for new claims, closed claims and claims in force for each month in turn.  So it would look like this:


      2017 Jun101
      2017 Jul212
      2017 Aug406
      2017 Sept327
      2017 Oct5111


      The claims in force is simple, by using set analysis to remove the DataAsAt filed for that field I can get the current in force claims.  But for new, I need to select the DecisionAuthorisationDate.YearMonth = DataAsAt.YearMonth as well as the DecisionReason='New Payment' at the same time as removing the DataAsAt filter.


      How do I set the two fields to be equal in set analysis?  Or am I looking at this the wrong way?


      Many thanks