Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Navars
Creator
Creator

holidays calculation for running year by using network days function

Hello All,

I am trying to find out the working days in a month for current year, for that i am wring the below script.

i am getting two records for each month one is total no.of working days (Excluding Weekends) and second is after holidays calculation.

but i need only one record that holidays include.

Script:

temp_network_days:
Load distinct [Call Date],
MonthStart([Call Date]) as monthstart1,
MonthEnd([Call Date]) as monthend1
Resident MasterCalendar
where [Call Date]>= Date(YearStart(Today()), 'DD/MM/YYYY'); // need to automate

left join(temp_network_days)
Load date(date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as Date1,
date(date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as [Call Date];

Load * inline [
Date1,Day,Holiday
1/1/2022,Sat,New Year's Day
2/1/2022,Sun,Berchtold's Day
6/1/2022,Thu,Epiphany
1/3/2022,Tue,Republic Day
19/3/2022,Sat,St Joseph's Day
7/4/2022,Thu,Näfelser Fahrt
15/4/2022,Fri,Good Friday
18/4/2022,Mon,Easter Monday
1/5/2022,Sun,Labour Day
26/5/2022,Thu,Ascension Day
6/6/2022,Mon,Whit Monday
16/6/2022,Thu,Corpus Christi
23/6/2022,Thu,Independence of Jura
29/6/2022,Wed,St Peter and St Paul
1/8/2022,Mon,National Day
15/8/2022,Mon,Assumption Day
8/9/2022,Thu,Jeûne genevois
19/9/2022,Mon,Day after the Federal Fast
22/9/2022,Thu,St Mauritius
25/9/2022,Sun,Brother Klaus Festival
1/11/2022,Tue,All Saints' Day
8/12/2022,Thu,Immaculate Conception
24/12/2022,Sat,Christmas Eve
25/12/2022,Sun,Christmas Day
26/12/2022,Mon,St Stephen's Day
26/12/2022,Mon,Christmas Holiday
31/12/2022,Sat,New Year's Eve
];


network_days:
Load distinct [Call Date] ,
Date1,
monthstart1,
monthend1,
NetWorkDays(monthstart1,monthend1,[Call Date]) as No_of_days
Resident temp_network_days;

drop table temp_network_days;

 

current Output:

Navars_0-1662616865057.png

expected output is single record for month, which are highlighted in yellow for eg.

Thanks in Advance.

 

Labels (6)
0 Replies