Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

datetime stamp duration

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.


6 Replies
New Contributor III

Re: datetime stamp duration

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.


Re: datetime stamp duration


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:MMSmiley FrustratedS TT'),'DD-MM-YYYY HH:MMSmiley FrustratedS TT')

-Timestamp(Date(Date#('07-03-2017 10:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MMSmiley FrustratedS TT'),'DD-MM-YYYY HH:MMSmiley FrustratedS TT'),'HH:MMSmiley FrustratedS')


Re: datetime stamp duration


Try this expression for getting the correct time interval..

Not applicable

Re: datetime stamp duration

This is what happens

should be by person by day

Not applicable

Re: datetime stamp duration

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.

New Contributor III

Re: datetime stamp duration

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 belowdates.jpg

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