3 Replies Latest reply: Apr 7, 2017 11:32 AM by Simon Touyet

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?

• Re: Set analysis formula with common field not working

Can you provide 10 lines code for both tables?

• 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 ID Account ID Start Date End Date 1 ABC 03/04/2015 02/04/2016 2 GHJ 13/11/2015 12/11/2016 3 KLM 01/01/2014 31/12/2014 4 KLM 01/01/2015 31/12/2015 5 KLM 01/01/2016 30/12/2016

Case Data

 Case Number Opened Date Account ID 32073 05/04/2016 ABC 31721 04/09/2016 GHJ 26867 01/12/2016 GHJ 25998 04/11/2016 KLM 39781 04/03/2017 KLM
• 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,