Qlik Community

Ask a Question

QlikView Integrations

Discussion Board for collaboration on QlikView Integration.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
j_nourbakhsh
Contributor
Contributor

Calculate Business hours between two dates

hi,

with my solution you can calculate Business hours between two dates:

- Take out the holidays

- Determine your beginning and end of working days

- and ...

 

for use:

- use a master calendar and defined  Business hours start and end, flag holidays and ...

M_DateWeekDayBusStartTimeBusEndTimeWorkTimeIsHoliday
2021/03/21200:0000:0000:001
2021/03/22300:0000:0000:001
2021/03/23400:0000:0000:001
2021/03/24500:0000:0000:001
2021/03/25608:0012:0004:000
2021/03/26700:0000:0000:001
2021/03/27108:0016:3008:300
2021/03/28208:0016:3008:300
2021/03/29300:0000:0000:001
2021/03/30408:0016:3008:300
2021/03/31508:0016:3008:300
2021/04/01600:0000:0000:001
2021/04/02700:0000:0000:001
2021/04/03108:0016:3008:300
2021/04/04208:0016:3008:300
2021/04/05308:0016:3008:300
2021/04/06408:0016:3008:300
2021/04/07508:0016:3008:300
2021/04/08608:0012:0004:000
2021/04/09700:0000:0000:001
2021/04/10108:0016:3008:300
2021/04/11208:0016:3008:300
2021/04/12308:0016:3008:300
2021/04/13408:0016:3008:300
2021/04/14508:0016:3008:300
2021/04/15608:0012:0004:000
2021/04/16700:0000:0000:001
2021/04/17108:0016:3008:300
2021/04/18208:0016:3008:300
2021/04/19308:0016:3008:300
2021/04/20408:0016:3008:300

 

- use subroutine and load data:

 

 

Sub BusinessWorkTime(vTableName, vCaseId, vStartTime, vEndTime)

	Calendar: LOAD M_Date, BusStartTime, BusEndTime, WorkTime
	FROM [.\Calendar.xlsx](ooxml, embedded labels, table is Cal);
	
	tmp1:
	NoConcatenate LOAD
		[$(vCaseId)] as CaseId
		,[$(vStartTime)] as Start
		,[$(vEndTime)] as End
		,Time(Frac([$(vStartTime)])) as StartTime
		,Time(Frac([$(vEndTime)])) as EndTime
	Resident [$(vTableName)];
	
	Left Join IntervalMatch ( M_Date )
	LOAD Floor(Start), End Resident tmp1;
	
	Left Join (tmp1) LOAD * Resident Calendar;
	
	tmp2:
	NoConcatenate LOAD *,
		Time(if( M_Date > Floor(Start) and M_Date <> Floor(End) , WorkTime,
		if( M_Date = Floor(Start) and M_Date <> Floor(End) , if(StartTime > BusEndTime, 0 , BusEndTime-RangeFractile(0.5,StartTime,BusStartTime,BusEndTime)),
		if( M_Date = Floor(End) and M_Date <> Floor(Start) , if(EndTime < BusStartTime, 0 , RangeMin(EndTime,BusEndTime)-BusStartTime),
		if( M_Date = Floor(End) and M_Date = Floor(Start) , RangeMin(EndTime,BusEndTime)-RangeFractile(0.5,StartTime,BusStartTime,BusEndTime),
		'NA' ))))) as WorkTimeAdj
	Resident tmp1;
	
	Left Join ([$(vTableName)])
	LOAD
		CaseId as [$(vCaseId)],
		Interval(Sum(WorkTimeAdj)) as BusinessCycleTime
	Resident tmp2
	Group by CaseId;
	
	DROP Tables Calendar,tmp1,tmp2;

End Sub
//==============================================
Data:
LOAD id ,
     Created,
     Resolved,
	 Interval(Resolved-Created) as CycleTime
FROM [.\Ticket.xlsx](ooxml, embedded labels, table is Sheet1);

Call BusinessWorkTime('Data','id','Created','Resolved');

 

 

 

and check result:

idCreatedResolvedCycleTimeBusinessCycleTime
129764462021/04/11 10:13:092021/04/11 10:23:190:10:100:10:10
129758802021/04/11 08:31:362021/04/11 12:48:264:16:504:16:50
129743232021/04/10 18:34:592021/04/10 18:48:220:13:230:00:00
129741912021/04/10 17:59:192021/04/10 19:02:261:03:070:00:00
129714592021/04/10 10:57:362021/04/10 18:00:147:02:385:32:24
129710132021/04/10 09:47:062021/04/11 07:40:1021:53:046:42:54

 

Does anyone have a better idea for calculating Business hours between two dates?

 

Labels (1)
1 Solution

Accepted Solutions
1 Reply