Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

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.

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