Holidays exclusion

[Anonymous]

Friends,

I need to exclude the public holidays and calculate hours of only working days. I have the list of holidays so, plz help me by giving code to achieve this and also plz explain it...

[rahulpawarb]

Hello Siva,

If you don't want to include Public Holidays in the master calendar table then include below statement into filter section.

WHERE NOT Match(Date,'1/26/2017','3/13/2017','3/28/2017', '5/1/2017','6/26/2017','8/15/2017','8/25/2017','10/2/2017','10/19/2017','10/20/2017','12/25/2017');

Note:

I have considered the public holiday list mentioned in my earlier thread.

Also pay close attention towards value list in Match function. It should be exact match with values from Master Calendar table. For example, 26th January 2017 is listed as 1/26/2017 in calendar table  then mention '1/26/2017' in Match function list. Do not use '01/26/2017' it won't consider it.

Regards!

Rahul

[Anonymous]

Thank you so much guys...

[David_K]

Hi,

Thanks for the information, and sample script, however, when I try using this changing the dates to my local UK  market format, the holidays are not taken into account, and I get the basic calculation of the number of NetWorkDays.

SCRIPT

Holidays:

LOAD * INLINE [
Holidays
'01/01/2021'
'02/04/2021'
'05/04/2021'
'03/05/2021'
'31/05/2021'
'30/08/2021'
'27/12/2021'
'28/12/2021'
'01/01/2022'
'03/01/2022'
'15/04/2022'
'18/04/2022'
'02/05/2022'
'02/06/2022'
'03/06/2022'
'29/08/2022'
'26/12/2022'
'27/12/2022'
];

Holidays2:

Load

concat(Holidays, ',') as ALL_HOLIDAYS Resident Holidays;

Let vHolidays = Peek('ALL_HOLIDAYS');

Drop table Holidays2;

EXPRESSION

NetWorkDays (START DATE , now() , $(vHolidays))

If I add the holiday dates directly in the expression it works as I would expect it to. 

Any additional advice would be great.

Thanks,

David