Set Analysis with Dates

Anonymous — Fri, 08 Dec 2017 19:45:07 GMT

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

Re: Set Analysis with Dates

sunny_talwar — Fri, 08 Dec 2017 19:52:55 GMT

Would you be able to share some raw data to see how the data looks like?

Re: Set Analysis with Dates

rubenmarin — Fri, 08 Dec 2017 20:09:19 GMT

Hi Chris, If I understood it right flagging the records with DecisionAuthorisationDate.YearMonth = DataAsAt.YearMonth will help to apply set analysis, ie:

Script:

If(MonthStart(DecisionAuthorisationDate)=MonthStart(DataAsAt), 1, 0) as isSameMonth

Expression:

Count({<isSameMonth={1}, DecisionReason={'New Payment'}>} ClaimID)

topic Re: Set Analysis with Dates in App Development

Set Analysis with Dates

Re: Set Analysis with Dates

Re: Set Analysis with Dates