Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to calculate the number of complete hours staff has worked at different times during the day. It is a 24/7 schedule.
Below is an example of how the data looks (there are thousands and thousands of rows like this). An Id that represent a staff member, and date/time when they start work and end work.
I am trying to figure out how to calculate the number of fully completed scheduled working hours that there are at different times during the day, i.e. between 0800 - 0900, 0900 - 1000, 2200 - 2300 (at all hours during the day). I am interested in both fully completed hours but also partly completed hours, i.e. start at 0730...or end at 1615.
I want to be able to select several days or weeks to see aggregated statistics for scheduled hours at different times during the day.
Right now I am stuck, so any help is appreciated.
//Michael
Id | Start_time | End_time |
A8213 | 2020-01-03 06:00 | 2020-01-03 15:15 |
A5232 | 2020-01-03 13:00 | 2020-01-03 23:00 |
A8231 | 2020-01-03 08:00 | 2020-01-03 16:30 |
A1212 | 2020-01-03 22:00 | 2020-01-04 07:00 |
A8213 | 2020-01-04 06:00 | 2020-01-04 16:00 |
A8231 | 2020-01-04 13:50 | 2020-01-04 23:00 |
A5232 | 2020-01-04 08:00 | 2020-01-04 16:00 |
A1212 | 2020-01-04 21:00 | 2020-01-05 07:30 |
A8213 | 2020-01-05 06:00 | 2020-01-05 16:00 |
A8231 | 2020-01-05 13:00 | 2020-01-05 22:00 |
A5232 | 2020-01-05 08:00 | 2020-01-05 16:45 |
A1212 | 2020-01-05 22:00 | 2020-01-06 07:00 |
This way I can heck if it's within an interval, but it doesn't feel like I am going about it in a QlikView way.
//Michael
CHECKHOUR:
LOAD
*,
If(Time#(tFrom, 'YYYY-MM-DD hh:mm')<Time#('08', 'hh') and Time#(tTo, 'YYYY-MM-DD hh:mm')>Time#('09', 'hh'), 1, 0) as [08-09],
If(Time#(tFrom, 'YYYY-MM-DD hh:mm')<Time#('12', 'hh') and Time#(tTo, 'YYYY-MM-DD hh:mm')>Time#('12', 'hh'), 1, 0) as [12-13],
If(Time#(tFrom, 'YYYY-MM-DD hh:mm')<Time#('15', 'hh') and Time#(tTo, 'YYYY-MM-DD hh:mm')>Time#('15', 'hh'), 1, 0) as [15-16],
If(Time#(tFrom, 'YYYY-MM-DD hh:mm')<Time#('22', 'hh') and Time#(tTo, 'YYYY-MM-DD hh:mm')>Time#('22', 'hh'), 1, 0) as [22-23]
;
Load Distinct
%KeyDate,
Subfield(%KeyDate,'_',1) as tFrom,
Subfield(%KeyDate,'_',2) as tTo
resident SCHEDULE
;