Business hours function

    Hi,

     

    Inspired by my current project requirements and Kushal Chawda Male's earlier post https://community.qlik.com/docs/DOC-16489 i created business hours calculation "function" using variable and Kushal 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.