topic Re: Networking dates excluding weekends and business dates in App Development

Networking dates excluding weekends and business dates

mimivan — Tue, 14 Feb 2023 01:16:02 GMT

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);

Re: Networking dates excluding weekends and business dates

Lech_Miszkiewicz — Tue, 14 Feb 2023 01:28:19 GMT

Hi,

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

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

Re: Networking dates excluding weekends and business dates

mimivan — Tue, 14 Feb 2023 04:33:12 GMT

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);

Re: Networking dates excluding weekends and business dates

mimivan — Tue, 14 Feb 2023 04:34:20 GMT

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

Re: Networking dates excluding weekends and business dates

Lech_Miszkiewicz — Tue, 14 Feb 2023 06:05:25 GMT

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:

Re: Networking dates excluding weekends and business dates

mimivan — Tue, 14 Feb 2023 20:34:06 GMT

worked well. Thanks!