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:
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?