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:
| Dimension | Measure 1 | Measure 2 | Measure 3 | Measure 4 | Measure 5 |
|---|
| Account ID | Number of cases | Number of entitlements | Oldest entitlement start date | Newest entitlement end date | How 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?
