Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
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