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.
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'
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
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:MM:SS TT'),'DD-MM-YYYY HH:MM:SS TT')
-Timestamp(Date(Date#('07-03-2017 10:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MM:SS TT'),'DD-MM-YYYY HH:MM:SS TT'),'HH:MM:SS')