Qlik Community

Ask a Question

QlikView Integrations

Discussion Board for collaboration on QlikView Integration.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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