Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

chrisweldon
New Contributor III

Set Analysis with Dates

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:

DataAsAtNewClosedInForce
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

Chris

2 Replies
MVP
MVP

Re: Set Analysis with Dates

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

Re: Set Analysis with Dates

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)

Community Browser