Hi there,
I'm new to Qlik and having trouble with the following dataset:
| Patient ID | Appointment Date | Doctor | Start time | End time | Appointment time |
| 1 | 1-Jan-20 | A | 9:00 AM | 10:00 AM | 1:00 |
| 2 | 1-Jan-20 | A | 11:00 AM | 11:30 AM | 1:00 |
| 3 | 1-Jan-20 | A | 11:00 AM | 12:15 PM | 1:15 |
| 4 | 1-Jan-20 | A | 12:00 PM | 1:00 PM | 1:00 |
| 5 | 1-Jan-20 | A | 4:00 PM | 5:00 PM | 1:00 |
| 6 | 1-Jan-20 | B | 10:00 AM | 11:30 AM | 1:30 |
| 7 | 1-Jan-20 | B | 4:00 PM | 5:00 PM | 1:00 |
| 8 | 1-Jan-20 | B | 4:00 PM | 5:00 PM | 1:00 |
I need to find out the actual work time for the practitioners,
Here,
For, Practitioner A - Total Appointment time = 4:45:00;
Actual Work Time = 4:00:00
For, Practitioner B - Total Appointment time = 3:30:00;
Actual Work Time = 2:30:00
Required Output:
| Appointment Date | Doctor | Actual Work Time |
| 1-Jan-20 | A | 4:00:00 |
| 1-Jan-20 | B | 2:30:00 |
I was thinking of,
Actual Work Time = Aggr(Sum(Interval( Min([Start Date]) - Max([End Date])) - (Total time of NO Work ))
However, I'm unsure of how to write the query for Total Time of No Work (i.e., when the Practitioner is not working)
Thanks in Advance for your help
Best Regards,
Atul
