How to show IDs which are in ready status within certain time range
I'm pretty new Qlik Sense user, and I'm making pivot table which shows those patients who have been in ready status between selected time range. I have columns OWNER_ID, START_TIME, END_TIME & STATUS. START_TIME & END_TIME are in hours, for example, 6 which means 6 o'clock.
OWNER_ID
column contains those patients who have "ready status".
For now my time selection is made with filter pane and you can select "0-2 o'clock", 3-5 o'clock", "6-8 o'clock" et cetera. When I select a time range, for example 6-8 o'clock, pivot table shows those patients whose start time is 6, 7 or 8, but it should show those who have been in ready status between 6 and 8 o'clock.
I have tried aggr() chart function like
=aggr(max({1<END_TIME={">=$(=min(EHour))"}>} END_TIME),END-TIME)
within Data -> Dimensions -> END_TIME -> Field, but that doesn't work - pivot table shows OWNER_IDs whose END_TIME is greater than given time range.
How to solve this?
Seems to me if you had a data table that looked like this:
| OWNER_ID | START_TIME | END_TIME | STATUS |
|---|---|---|---|
| 001 | 05:00 | 10:00 | Ready |
| 002 | 09:00 | 13:00 | Not Ready |
| 003 | 21:00 | 00:00 | Not Ready |
| 004 | 10:00 | 11:00 | Ready |
| 005 | 09:00 | 12:00 | Ready |
...and then a table with the hours information:
| START_TIME | ETIME | EHOUR |
|---|---|---|
| 00:00 | 01:00 | 0-2 o'clock |
| 01:00 | 02:00 | 0-2 o'clock |
| 02:00 | 03:00 | 0-2 o'clock |
| 03:00 | 04:00 | 3-5 o'clock |
| 04:00 | 05:00 | 3-5 o'clock |
| 05:00 | 06:00 | 3-5 o'clock |
| 06:00 | 07:00 | 6-8 o'clock |
| 07:00 | 08:00 | 6-8 o'clock |
| 08:00 | 09:00 | 6-8 o'clock |
| 09:00 | 10:00 | 9-11 o'clock |
| 10:00 | 11:00 | 9-11 o'clock |
| 11:00 | 12:00 | 9-11 o'clock |
| 12:00 | 13:00 | 12-14 o'clock |
| 13:00 | 14:00 | 12-14 o'clock |
| 14:00 | 15:00 | 12-14 o'clock |
| 15:00 | 16:00 | 15-17 o'clock |
| 16:00 | 17:00 | 15-17 o'clock |
| 17:00 | 18:00 | 15-17 o'clock |
| 18:00 | 19:00 | 18-20 o'clock |
| 19:00 | 20:00 | 18-20 o'clock |
| 20:00 | 21:00 | 18-20 o'clock |
| 21:00 | 22:00 | 21-23 o'clock |
| 22:00 | 23:00 | 21-23 o'clock |
| 23:00 | 00:00 | 21-23 o'clock |
...then you'd have an association between [START_TIME] and [EHOUR], such when you select [EHOUR], it correctly filters your data by the [START_TIME]. Then, you only have to adjust your Set Expression, which I think should look more like this:
Only({<STATUS={'Ready'}>} STATUS)
Here's a screenshot GIF of how it looks for me:
Hopefully I understood that correctly!