topic Re: Calculate total time duration for different tasks with exclusion of overlapping hours in App Development

Calculate total time duration for different tasks with exclusion of overlapping hours

ahmed_qlik — Tue, 21 Dec 2021 21:56:36 GMT

I am looking to calculate total working hours for a process which includes multiple tasks ( StartTime, EndTime) . Tricky part is that these tasks may go parallel also which means we will be having overlapping hours which we need to exclude.

For e.g

Task	Start Time(DD-MM-YYYY hh:mm:ss)	End Time(DD-MM-YYYY hh:mm:ss)
Task1	18-05-2021 8:00 am	18-05-2021 12:00 pm
Task2	18-05-2021 8:00 am	18-05-2021 10:00 am
Task3	18-05-2021 12:30 pm	18-05-2021 1:00 pm
Task4	18-05-2021 2:00 pm	18-05-2021 3:00 pm

Here total working hours are 5 hours 30 minutes

( 8-12 = 4 hours;

12:30-1:00 = 30 minutes ;

2-3=1 hour)

Note: We have to avoid all those hours which are overlapping e.g Task2 --> (8-10 ) as these hours were already part of (8-12) in task 1

Need a logic to calculate this total working hours

@Michael_Tarallo

@christof schwarz

Re: Calculate total time duration for different tasks with exclusion of overlapping hours

JuanGerardo — Wed, 19 May 2021 18:51:50 GMT

Hi @ahmed_qlik,

This can be a little bit tricky, but you can get it by joining the periods table with itself by looking at which periods overlap (start time in one period between start and end times in the other). I have created an example of how to get that overlapped periods:

Times: Load Task, Timestamp#(StartTime, 'DD-MM-YYYY hh:mm tt') AS StartTime, Timestamp#(EndTime, 'DD-MM-YYYY hh:mm tt') AS EndTime Inline [ Task StartTime EndTime Task1 18-05-2021 8:00 am 18-05-2021 12:00 pm Task2 18-05-2021 8:00 am 18-05-2021 10:00 am Task3 18-05-2021 12:30 pm 18-05-2021 1:00 pm Task4 18-05-2021 2:00 pm 18-05-2021 3:00 pm ] (delimiter is '\t'); Pass1: Load Distinct StartTime AS Start1, EndTime AS End1 Resident Times; Outer Join Load Distinct StartTime AS Start2, EndTime AS End2 Resident Times; Drop Table Times; Pass2: Load Start1 AS Start, Max(End1, End2) AS End Resident Pass1 Where (Start2 >= Start1 And Start2 <= End1 And End2 > End1) // Period 2 starts between Period 1 but ends later Or (Start1 = Start2 And End1 = End2) // Same Period, so gets not overlapped periods Group By Start1 ; Drop Table Pass1; Periods: Load Distinct 'Period ' & RowNo() AS Period, Timestamp(Start) AS Start, Timestamp(End) AS End, Timestamp(End - Start) AS Duration Resident Pass2 Order by Start; Drop Table Pass2; Tag Fields Start, End, Duration With $timestamp;

The result is:

I think it could be possible to add another pass for some casuistic not presented in the sample data, but it will be similar to Pass2.

Re: Calculate total time duration for different tasks with exclusion of overlapping hours

ahmed_qlik — Wed, 19 May 2021 23:42:49 GMT

Thanks Juan , really appreciate your quick support.

However if there is a condition where only some part of the duration is overlapped like

Task1, 18-05-2021 8:00 am, 18-05-2021 12:00 pm

Task7, 18-05-2021 10:00 am, 18-05-2021 12:30 pm

if you notice, Task 7 is overlapped with Task1 but not completely ( 30 minutes ).

What we should do in that condition?

Regards

Ahmed

Re: Calculate total time duration for different tasks with exclusion of overlapping hours

ahmed_qlik — Thu, 20 May 2021 09:03:32 GMT

I have modified it little bit ,

Pass1:
Load Distinct
//Port,
StartTime AS Start1,
EndTime AS End1
Resident Times;
Outer Join
Load Distinct
Port,
StartTime AS Start2,
EndTime AS End2
Resident Times;

Drop Table Times;

Pass2:
Load distinct
Port,
Start1 AS Start,
Max(End1, End2) AS End
Resident Pass1
Where
(Start2 >= Start1 And Start2 <= End1 And End2 > End1) // Period 2 starts between Period 1 but ends later
Or
(Start1 = Start2 And End1 = End2) // Same Period, so gets not overlapped periods

Group By
Port,
Start1 order by
Port,
Start1 asc
;

Drop Table Pass1;
//exit SCRIPT;
Pass3:
load *,
if (Port=Previous(Port) ,if( Start > Previous(Start) and End >=Previous(End) and Start<Previous(End), Start-Previous(End)),0) as New_duration
Resident Pass2 ;
drop Table Pass2;

//exit SCRIPT;

Periods:
Load Distinct
'Period ' & RowNo() AS Period,
Port,
Timestamp(Start) AS Start,
Timestamp(End) AS End,
Timestamp(End - Start) AS Duration,
New_duration
Resident Pass3
Order by Port,Start;

Drop Table Pass3;

//then adding duration and New_duration ; it seems working now but if there is a whole day difference between Start and end time, then it is not working.

Regards

Ahmed