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

    Set analysis formula with common field not working

    Simon Touyet

      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?