Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If a person clocks in and out for the first time (Same day)
In:06:49:00 | Out:16:05:00 | Time:09:16:00 |
and then clock in for the second time:
In: 19:52:00 | Out: 20:45:00 | Time:00:53:00 |
The actual active time will be 10:09 hours & min and not between 06:49-20:45 as some have given me suggestions of using max and min time.
How do I calculate the active time for each day when multiple reports exists?
Thanks in advance.
Daniel,
it would be good to see how the data model looks like, e.g. how in and out fields are related.
I assume that they are located in a single table with an addtional person id:
SET TimeFormat='hh:mm:ss';
LOAD *, Interval(Out-In) as Duration INLINE [
Person, In, Out
A,06:49:00,16:05:00
A,19:52:00,20:45:00
];
Now just create a chart in the frontend with dimension Person and as expression
=Sum(Duration)
returned result:
Person | Sum(Duration) |
10:09:00 | |
A | 10:09:00 |
You can also calculate the durations in the frontend, if needed:
=Interval( Sum( Out-In))
Hi, that is exactly what I'm looking for. I attach my document because the date looks a little bit different compared to the above.
Well, your IN and OUT are not located on the same record. If you have an identifier per event, you can use this identifier to bring both timestamps on the same record, maybe using JOIN or ApplyMap.
You can also use inter record functions like Previous() in the script to check the previous record and access values of the previous input table record.
See attached (using a binary load only because I don't have access to your sources).