Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try below
Data:
LOAD
"Patient ID",
"Appointment Date",
Doctor,
Time#("Start time",'hh:mm TT') as "Start time",
Time#("End time",'hh:mm TT') as "End time",
Time#("Appointment time",'hh:mm') as "Appointment time"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
New:
Load *,
rangesum(if(Peek(Doctor)=Doctor and Peek("Appointment Date")="Appointment Date",
if("Start time">Peek("End time"),
"Start time"-Peek("End time"),0))) as No_work_Hours
Resident Data
Order by Doctor,"Appointment Date","Start time";
Drop Table Data;
//Now on frond end create table
Dimensions:
1) Appointment Date
2) Doctor
Expression:
1)Actual Work Time
=time(sum(aggr((max([End time])-min([Start time])) -sum(No_work_Hours),[Appointment Date],Doctor)))
2) No Work Time
=time(sum(No_work_Hours))
Your data doesn't show [Total time of NO Work]. Can you add that data to your sample?
Hi,
[Total time of NO Work] is the time when the practitioner is free. It is something I need to calculate and I'm unable to understand the logic for the same.
Ex- in case of practitioner A,
Time of No work = 1 hr( Between 10:00 AM and 11: AM) + 3 hrs (Between 1 PM to 4PM)
So, the Actual Work Hours = Total Time Available - Time of No work
that is, 8 - 4 hours = 4 hours
for A, total time available isn't 9 (4 Am - 1Pm)>
Thanks for your response and correcting me Kush,
It was a wrong entry. It will be 4:00 PM to 5:00 PM. I have corrected it.
Hence, Available time is from 9:00 AM to 5:00 PM (8 hours)
try below
Data:
LOAD
"Patient ID",
"Appointment Date",
Doctor,
Time#("Start time",'hh:mm TT') as "Start time",
Time#("End time",'hh:mm TT') as "End time",
Time#("Appointment time",'hh:mm') as "Appointment time"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);
New:
Load *,
rangesum(if(Peek(Doctor)=Doctor and Peek("Appointment Date")="Appointment Date",
if("Start time">Peek("End time"),
"Start time"-Peek("End time"),0))) as No_work_Hours
Resident Data
Order by Doctor,"Appointment Date","Start time";
Drop Table Data;
//Now on frond end create table
Dimensions:
1) Appointment Date
2) Doctor
Expression:
1)Actual Work Time
=time(sum(aggr((max([End time])-min([Start time])) -sum(No_work_Hours),[Appointment Date],Doctor)))
2) No Work Time
=time(sum(No_work_Hours))
Thanks Kush! 🙂
Appt Date | Doctor | Patient ID | Start time | End time | No Work Time |
1:30 | |||||
6/16/2020 | A | 1 | 8:15:00 | 9:00:00 | 0:00 |
6/16/2020 | A | 2 | 9:00:00 | 10:00:00 | 0:00 |
6/16/2020 | A | 3 | 9:15:00 | 9:30:00 | 0:00 |
6/16/2020 | A | 4 | 10:00:00 | 11:00:00 | 0:00 |
6/16/2020 | A | 5 | 10:00:00 | 10:30:00 | 0:30 |
6/16/2020 | A | 6 | 11:00:00 | 12:00:00 | 0:00 |
6/16/2020 | A | 7 | 13:00:00 | 14:00:00 | 1:00 |
6/16/2020 | A | 8 | 14:00:00 | 14:30:00 | 0:00 |
6/16/2020 | A | 9 | 14:00:00 | 14:45:00 | 0:00 |
6/16/2020 | A | 10 | 14:30:00 | 15:30:00 | 0:00 |
6/16/2020 | A | 11 | 14:30:00 | 15:30:00 | 0:00 |
Hi Kush,
While most of the output is correct, I'm getting the wrong output for many such cases (marked in 'red'). Can't figure out why this is happening. Can you please help?
Thanks,
Atul
How you are getting 30 Minutes no work as he already attended Patient from 10 to 11 AM? should it now be 30 minutes for patient id 6?
That is the error. It should have been "0 mins" instead of 30 min for patient id - 5. There are a few anomalies like this else, the logic you gave works fine for other records. "No work time" for patient id - 6 will be zero and is correct