Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

sum intervals in a pivot table

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

];

SumInterval.PNG

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

Thanks in advance.

JustusJonas

1 Solution

Accepted Solutions

Re: sum intervals in a pivot table

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


Capture.PNG

View solution in original post

2 Replies

Re: sum intervals in a pivot table

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


Capture.PNG

View solution in original post

Not applicable

Re: sum intervals in a pivot table

Great! Works fine. Thank you Sunny.