This data is just an example, it is not normalized on purpose.
I am very new to Qlik, although I have extensive experience in Excel and SQL and some Tableau experience.
Consider this table:
| Ticket_ID | Work_Start_Date | Work_End_Date | Priority |
| 1 | 1/1/2021 | 6/29/2021 | High |
| 2 | 3/21/2021 | 6/29/2021 | Medium |
| 2 | 1/1/2021 | 3/20/2021 | High |
| 3 | 1/1/2021 | 4/1/2021 | Low |
| 4 | 4/1/2021 | 6/29/2021 | Medium |
I would like to select the status as-of 3/31/2021, so the results would look like this:
| Ticket_ID | Work_Start_Date | Work_End_Date | Priority |
| 1 | 1/1/2021 | 6/29/2021 | High |
| 2 | 3/21/2021 | 6/29/2021 | Medium |
| 3 | 1/1/2021 | 4/1/2021 | Low |
Ticket_ID = 4 does not appear because it was not active as-of 3/31/2021.
The as-of date is a variable and may change to 4/30/2021, like so:
| Ticket_ID | Work_Start_Date | Work_End_Date | Priority |
| 1 | 1/1/2021 | 6/29/2021 | High |
| 2 | 3/21/2021 | 6/29/2021 | Medium |
| 4 | 4/1/2021 | 6/29/2021 | Medium |
Ticket_ID = 3 does not appear because it was not active as-of 4/30/2021.
Ultimately I'd like to present a KPI badge with a count or sum of 3 tickets on either date. The logic in Excel would be SUM(IF(AND(AsOfDate>=Work_Start_Date,AsOfDate<=Work_End_Date),1,0)).
I would also like to enter a date as a variable on a sheet so the user may pick their as-of date and it calculates as the field is selected or updated. I do not know how to create such an input. I have used a filter pane with the Work_Start_Date and Work_End_Date but that only evaluates the values themselves and not as-of the date variable.
I have pre-sorted the data by Ticket_ID, Work_End_Date, Work_Start_Date in Excel and loaded the .xlsx file to Qlik.
I imagine I could load the data in a certain way (maybe use FirstSortedValue?), and then use a formula inside the KPI to get the correct number. Would that be the best way to go about it?
Thank you!
