sum intervals in a pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hello!

I'd like to sum the working time per day in a pivot table. Everything is fine in my pivot, but I failed at summing up the working time interval.

Explanation of the sample:

Every WorkerID has a start time on one day. If there isn't an action 1 (approach), the will be a start time in action 2 (start of work). The working time interval is from start time of action 1 (or 2 if 1 is null) till the end time of action 2. I want to get the minimum start time of each day and the maximum end time of each day. Because every work can do multiple work tours on one day. The interval between these times is his working time. Now I would like to sum these intervals for each worker per day.

I think, this problem can't be to hard and I am only to blind to see the solution for the formular of the interval column:

Interval(If(isnull(StartOfApproach),EndOfWork-StartOfWork,EndOfWork-StartOfApproach))

This ones doesn't work neither:

Sum(Interval(If(isnull(StartOfApproach),EndOfWork-StartOfWork,EndOfWork-StartOfApproach)))

Script:

LOAD * INLINE [

WorkerID, Date, StartTime, EndTime, Action

1001, 29.06.2016, 08:15, 08:45, 1

1001, 29.06.2016, 08:55, 15:15, 2

1001, 29.06.2016, 16:15, 16:20, 1

1001, 29.06.2016, 16:30, 17:15, 2

1002, 30.06.2016, 07:45, 08:45, 1

1002, 30.06.2016, 08:50, 15:35, 2

1002, 01.07.2016, 08:30, 08:40, 1

1002, 01.07.2016, 08:45, 15:15, 2

1003, 30.06.2016, 08:30, 12:15, 2

1003, 01.07.2016, 08:00, 08:05, 1

1003, 01.07.2016, 08:30, 11:15, 2

1003, 02.07.2016, 08:30, 11:35, 2

];

There sum result for 1003 has to be 10:05:00 and for 1002 14:35:00.

Thanks in advance.

JustusJonas

Re: sum intervals in a pivot table

sunny_talwar — Wed, 29 Jun 2016 16:31:37 GMT

Use this expression:

Interval(Sum(Aggr(

If(isnull(Min({$<Action = {1}>} StartTime)),Max({$<Action = {2}>} EndTime)-Min({$<Action = {2}>} StartTime),Max({$<Action = {2}>} EndTime)-Min({$<Action = {1}>} StartTime)), WorkerID, Date)))

Re: sum intervals in a pivot table

Wed, 29 Jun 2016 19:28:56 GMT

Great! Works fine. Thank you Sunny.

topic Re: sum intervals in a pivot table in QlikView

sum intervals in a pivot table

Re: sum intervals in a pivot table

Re: sum intervals in a pivot table