topic Re: Time reporting clock in / clock out (Multiple entries) in QlikView

Time reporting clock in / clock out (Multiple entries)

Mon, 21 Mar 2016 20:57:46 GMT

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.

Re: Time reporting clock in / clock out (Multiple entries)

swuehl — Tue, 22 Mar 2016 09:14:20 GMT

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))

Re: Time reporting clock in / clock out (Multiple entries)

Thu, 24 Mar 2016 14:14:22 GMT

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.

Re: Time reporting clock in / clock out (Multiple entries)

swuehl — Thu, 24 Mar 2016 14:33:36 GMT

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).