Discussion board where members can get started with Qlik Sense.
I am trying to get duration from datetime stamps but I have some employees that work evening from 10pm until 7am the following day.
Is there way to get the true duration? The last AM time and the first PM time I do not want to include. for this person it shows 23 hours of work. when in fact its closer to 10 hours.
Hi Robert, probably best to explain your data and how it is used. I am assuming you have a dimension of each person and measures of their login/logout times.
With the times you have shown, the user has logged out at 6:53 PM and then logged in again at 9:38 PM (or is it at 6:51 AM -> 9:55PM)?
So the problem is that your logic max(date)-min(date) works for daytime workers but not overnight workers. Is there a way to determine those workers in your data and then user a different formula? I'd argue you might be able to use a tricky formula to determine if the first login time (say after a weekend) is either in the morning or evening, which then determines the working hours based on starting in either of those periods, then apply the formula at that point, but ideally the logic would be already in your data.
Try this Set Analysis for getting time interval
=Interval(Timestamp(Date(Date#('08-03-2017 07:00:00 AM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MMS TT'),'DD-MM-YYYY HH:MMS TT')
-Timestamp(Date(Date#('07-03-2017 10:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MMS TT'),'DD-MM-YYYY HH:MMS TT'),'HH:MMS')
is there a way to have it use the max and min over twelve hours or split by AM and PM
What these people do is check prescriptions and the do 50 or more an hour so not to worried about the time between two prescriptions at 12:00 am or 12 pm.
Thanks for that - the timestamps in the middle need to be ignored. I think the easiest way to do this is in the loadscript, as it's getting messy in my table, but I think I'm getting close.
With this test data:
ts as PersonTimeRaw,
Timestamp#(ts, 'DD/MM/YYYY hh:mm:ss') as PersonTime
inline [ PersonID, ts
1, '12/02/2016 06:51:39'
1, '12/02/2016 06:53:37'
1, '12/02/2016 21:38:40'
1, '12/02/2016 21:54:01'
1, '12/02/2016 21:55:49'
1, '13/02/2016 02:40:12'
2, '12/02/2016 09:01:25'
2, '12/02/2016 10:31:25'
2, '12/02/2016 17:12:46'
I've created a table as below
So for dimensions, I have the PersonID, the day of the PersonTime, and the AM/PM flag
The next dimesion is the 12-hour end period from that AM/PM,
date(makedate(year(PersonTime), month(PersonTime), day(PersonTime) + if(floor(hour(PersonTime)/12) = 1, 1, 0)), 'DD/MM/YYYY') & ' ' &
time(makeTime(if(floor(hour(PersonTime)/12) = 1, 0, 12), 0, 0), 'hh:mm:ss')
, 'DD/MM/YYYY hh:mm:ss')
Then there are min/max and interval calculations afterwards
So the question is, by splitting up the min/max of the timestamp into AM/PM sections, is it useful? Possibly not, since you might be able to work out the same thing based on the hour values - we can work out if the min(timestamp) = max(timestamp) in that period that the endpoint of that time is in the next period (so you would add together the interval of the max(timestamp) -> AM/PM and AM/PM -> min(timestamp) of the next period, but that is getting complicated quickly.
Hopefully that's a good starting point, but it might just be simpler if you could assume anyone's timestamp after (say 7PM) is a nighttime worker