How to Get NetworkDays excluding Sunday and holidays

deepanshuSh — Mon, 09 Oct 2023 07:11:46 GMT

Hi, Fellow Qlikkers

Below is a modified script for getting the network days without saturday and non-working-days (holidays)

SET vFirstDate = date#('2023-01-01','YYYY-MM-DD');
SET vLastDate = date#('2024-01-01','YYYY-MM-DD');

//Holidays of the year List
Holidays:
mapping LOAD date#(Date,'YYYY-MM-DD') as Date,1 INLINE [
Date
2023-01-01
2023-03-18
2023-10-24
2023-10-23
];
//Inplace of inline load the excel of the holidays,
//data can be automated using the same prefix and suffix for files for each year

//All dates autogenerated between the date that you want to find the network days
Dates:
Load
date($(vFirstDate)+rowno()-1) as Date
autogenerate($(vLastDate)-$(vFirstDate)+1);

Working_Dates:
Load
Date,
weekDay(Date) AS Day,
if(match(num(weekday(Date)),0) // Sunday non-working //0 = default offset value for sunday in qliksense
or applymap('Holidays',Date,0)=1, // Filtering out holidays out of the total dates.
0, // Add zero as it is holiday
1) // Add one as it is working day
as working_nonWorking_tag
resident Dates;
drop table Dates;

//Use sum (working_nonWorking_tag) to get the working days

topic How to Get NetworkDays excluding Sunday and holidays in App Development

How to Get NetworkDays excluding Sunday and holidays