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:
| DataAsAt | New | Closed | InForce |
|---|
| 2017 Jun | 1 | 0 | 1 |
| 2017 Jul | 2 | 1 | 2 |
| 2017 Aug | 4 | 0 | 6 |
| 2017 Sept | 3 | 2 | 7 |
| 2017 Oct | 5 | 1 | 11 |
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
Chris
