sum intervals in a pivot table

[#1]

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

[sunny_talwar · Accepted solution]

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

[#3]

Great! Works fine. Thank you Sunny.