Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set analysis formula with common field not working

Hi all,

I have data like this:

Entitlements
Entitlement ID
Account ID
Start Date
End Date

Cases
Case Number
Opened Date
Account ID

I am trying to figure out how many cases were open while the account had a valid entitlement per broken down by account.

Some accounts will have multiple Entitlements and/or cases.

I have therefore created a sheet with a table in QlikSense and I setup very very simple measures so far to ensure the data is correct as such:

DimensionMeasure 1Measure 2Measure 3Measure 4Measure 5
Account IDNumber of casesNumber of entitlementsOldest entitlement start dateNewest entitlement end dateHow many cases were created while the account had a valid entitlement?
=count([Case Number])=count([Entitlement ID])=Min([Start Date])=Max([End Date])=Count({<[Start Date]={'<=$(Min([Opened Date]))'},[End Date]={'>=$(Max([Opened Date]))'}>} [Case Number])

Measure 5 is not working. It is always either giving a 0 result (if the account had no entitlement) or it gives the same result as Measure 1 (if the account had at least one entitlement).

I am guessing that it is not working as intended and instead of looking at the case's associated account's min start date (measure 3) or end date (Measure 4), it is looking as the entirety of the Entitlements Table and thus it is not accurate.

How can I fix this?

3 Replies
Highlighted
MVP
MVP

Can you provide 10 lines code for both tables?

Highlighted
Specialist
Specialist

Are you looking for Min of three dates ?

min( RangeMin( "Start Date", "End Date, "Opened Date" )) as minDate,

Highlighted
Contributor
Contributor

Sure.

We are talking about close to half a million lines in each table but let me get you an exerp that will illustrate the issue.

Entitlement Data

Entitlement IDAccount IDStart DateEnd Date
1ABC03/04/201502/04/2016
2GHJ13/11/201512/11/2016
3KLM01/01/201431/12/2014
4KLM01/01/201531/12/2015
5KLM01/01/201630/12/2016

Case Data

   

Case NumberOpened DateAccount ID
3207305/04/2016ABC
3172104/09/2016GHJ
2686701/12/2016GHJ
2599804/11/2016KLM
3978104/03/2017KLM