Qlik Community

QlikView Documents

Documents for QlikView related information.

Business hours function

jelen_michal
New Contributor II

Business hours function

Hi,

Inspired by my current project requirements and kush141087 earlier post https://community.qlik.com/docs/DOC-16489 i created business hours calculation "function" using variable and kush141087 calculation logic with minor adjustments.

This way it can be used dynamically within a LOAD script (actual example in the attached file).


SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

NoConcatenate

Holidays2016UK:

LOAD * INLINE [
date
01/01/2016
25/03/2016
28/03/2016
02/05/2016
30/05/2016
29/08/2016
27/12/2016
]
;

NoConcatenate

tmpConcatHolidays2016UK:
LOAD concat(chr(39) & date & chr(39),',') as HolidayDates
Resident Holidays2016UK;

Let vPublicHolidays2016UK = fieldvalue('HolidayDates',1); //variable will store concatenated public holidays dates list, to be used in networkday function to exclude holidays from calculation


SET vBusinsessHours =  

  //$1 - start timestamp (timestamp format)
//$2 - end timestamp (timestamp format)
//$3 - business_hours_start (num)
//$4 - business_hours_end (num)@
  rangesum(rangesum(
  NetWorkDays($1+1,$2-1,$(vPublicHolidays2016UK)) * MakeTime($4-$3),
// In between hours
  if(NetWorkDays($2,$2,$(vPublicHolidays2016UK)),
  Rangemin(rangemax(frac($2),maketime($3)),maketime($4))-
  Rangemax(rangemin(frac($2),maketime($3)),maketime($3)),0),
// working hours last day
  if(NetWorkDays($1,$1,$(vPublicHolidays2016UK)),
  Rangemin(rangemax(frac($1),maketime($4)),maketime($4))-
  Rangemax(rangemin(frac($1),maketime($4)),maketime($3)),0),
// working hours first day
  if(NetWorkDays($1,$1,$(vPublicHolidays2016UK)) and floor($1)=floor($2),-MakeTime($4-$3)) // If same day  then correct the hours
  )*24);


//Example usage:

Let vExampleResult = $(vBusinsessHours('24/8/2010 07:00:00','24/8/2010 14:00:00',8,18));


//OR in LOAD statememnt:

NoConcatenate
Time:
LOAD TicketNo,StartTime, EndTime, $(vBusinsessHours(StartTime,EndTime,8,18)) as BusinessHours
;
LOAD * Inline  [
TicketNo,StartTime, EndTime
1, 25/8/2010 15:00:00, 27/8/2010 18:00:00
2, 16/8/2010 10:00:00, 17/8/2010 13:00:00
3, 17/8/2010 13:30:00, 17/8/2010 14:45:00
4, 17/8/2010 15:00:00, 18/8/2010 17:00:00
5, 18/8/2010 17:01:00, 19/8/2010 16:00:00
6, 19/8/2010 17:00:00, 20/8/2010 10:00:00
7, 20/8/2010 11:00:00, 20/8/2010 17:00:00
8, 23/8/2010 14:00:00, 23/8/2010 16:00:00
9, 23/8/2010 17:00:00, 23/8/2010 18:00:00
10, 24/8/2010 7:00:00, 24/8/2010 14:00:00
11, 20/8/2010 17:30:00, 23/8/2010 13:00:00
12, 7/3/2016 16:00:00, 10/3/2016 18:00:00
13, 19/8/2010 11:00:00, 20/8/2010 18:30:00
14, 31/12/2015 00:00:00, 1/1/2016 18:30:00
15, 31/12/2015 00:00:00, 2/1/2016 10:00:00
16, 1/1/2016 03:50:00, 3/1/2016 10:34:00
17, 1/1/2016 00:00:00, 4/1/2016 18:30:00]
;



//I found a bug in previous published version. While passing holidays via parameter ($5) it passign only 1st date.Its fixed when passing directly via parameter to the function definition.


Attachments
Comments
jelen_michal
New Contributor II

//I found a bug in previous published version (<=3). While passing holidays via parameter ($5) it was passing only 1st date.Its fixed when passing holidays directly via parameter to the function definition.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2016-10-20 10:04 AM
Updated by: