Networking dates excluding weekends and business dates

[mimivan]

I want to calculate net working dates between 2 dates, start dated(e.g Dec 27 2022) and end dates(Jan 5 2023), which will not count the weekends and holiday on Jan 2.    I have a  holiday table that save all the holidays on a weekday that should be excluded.  How can I add the holiday date list to the NetWorkDays function ?  If I cannot, how can i exclude the business days?

let vnetworkdays = NetWorkDays(start_date, end_date);

[Lech_Miszkiewicz]

Hi, 

read definition of networkdays function: https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun...

after reading it you see that function has 3rd parameter: holidays - which needs to be pouplated as array of comma seperated values of holiday dates. To bring those dates from your table you can just use Concat function and pass them all to variable. If you dont know Concat function you can use the same help page to seek its description.

cheers

[mimivan]

Suppose to return 12 that skip 2 days holiday, but returning 14.  You know why ? Thanks.

Holidays_table:
LOAD Concat(Holidays,',') as Holidays_value;
Load * inline [
Holidays
04/10/2020
04/13/2020
05/04/2020
05/25/2020
08/31/2020
12/25/2020
12/28/2020
01/01/2021
04/02/2021
04/05/2021
05/03/2021
05/31/2021
08/30/2021
12/27/2021
12/28/2021
01/03/2022
04/15/2022
04/18/2022
05/02/2022
06/02/2022
06/03/2022
08/29/2022
12/26/2022
12/27/2022
01/02/2023
02/14/2023
04/07/2023
04/10/2023
05/01/2023
05/29/2023
08/28/2023
12/25/2023
12/26/2023
];

let vHolidays = Peek('Holidays_value',0,'Holidays_table');

let v2 = networkdays ('12/19/2013', '01/07/2014', vHolidays);

[mimivan]

I trace the vHolidays value  : 01/01/2021,01/02/2023,01/03/2022,02/14/2023,04/02/2021,04/05/2021,04/07/2023,04/10/2020,04/10/2023,04/13/2020,04/15/2022,04/18/2022,05/01/2023,05/02/2022,05/03/2021,05/04/2020,05/25/2020,05/29/2023,05/31/2021,06/02/2022,06/03/2022,08/28/2023,08/29/2022,08/30/2021,08/31/2020,12/25/2020,12/25/2023,12/26/2022,12/26/2023,12/27/2021,12/27/2022,12/28/2020,12/28/2021

[Lech_Miszkiewicz · Accepted solution]

the code above will not work as you are using 2013 and 2014 years and holidays you have for 2022-2023

below is a working sample i quickly did changing some obvious formats to meet requirements listed in help document:

as mentioned in help page you must check format of your Date system variable as the same format must be used in networkdays function:

[mimivan]

worked well. Thanks!