Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
simontouyet
New 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
MVP
MVP

Re: Set analysis formula with common field not working

Can you provide 10 lines code for both tables?

galax_allu
Valued Contributor

Re: Set analysis formula with common field not working

Are you looking for Min of three dates ?

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

simontouyet
New Contributor

Re: Set analysis formula with common field not working

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