# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Partner

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

Hi

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

@rwunderlich

@marcus_sommer

Labels (1)
• ### Other

3 Replies
Partner

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:
Timestamp#(StartTime, 'DD-MM-YYYY hh:mm tt')	AS StartTime,
Timestamp#(EndTime, 'DD-MM-YYYY hh:mm tt')		AS EndTime
Inline [
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:
StartTime	AS Start1,
EndTime		AS End1
Resident Times;
Outer Join
StartTime	AS Start2,
EndTime		AS End2
Resident Times;

Drop Table Times;

Pass2:
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:
'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.

JG

Partner
Author

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

Partner
Author

I have modified it little bit ,

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

Drop Table Times;

Pass2:
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:
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:
'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

Tags
Community Browser