As-of date picking active entry between date range
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?
