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...

[tresesco]

NetWorkDays() is the function that could help you here. Go through : https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkd...

[sdmech81]

Use function NetWorkDays()..

Here suppose u want working days btn dates A and B then you should say like

networkdays ('A', 'B',)

and also if u want to pass on holidays other default values you should pass those values like say n1,n2 then,

networkdays ('A', 'B','n1','n2')

Try n hope this helped..

[rahulpawarb]

Hello Siva,

You can use below sample code snippet:

NetWorkDays(StartDate,EndDate, '26/01/2017','13/03/2017','28/03/2017', '01/05/2017','26/06/2017','15/08/2017','25/08/2017','02/10/2017','19/10/2017','20/10/2017','25/12/2017') * DailyHrs As PaidHrs

Below are considered Public Holidays:

TITLE	DATE
Republic Day	Thursday, January 26, 2017
Holi	Monday, March 13, 2017
Gudi Padwa ,Ugadi	Tuesday, March 28, 2017
Maharastra DAY/ May Day	Monday, May 1, 2017
Eid al-Fitr ,Ramadan	Monday, June 26, 2017
Independence Day	Tuesday, August 15, 2017
Ganesh Chaturthi	Friday, August 25, 2017
Gandhi Jayanti	Tuesday,October 2, 2017
Diwali	Thursday, October 19, 2017
Diwali - Govardhan	Friday, October 20, 2017
Christmas	Monday, December 25, 2017

Regards!

Rahul

[zhadrakas]

you can make your life easier. if you have a list of all Holidays, concat them into one field.

Holidays:
LOAD * INLINE [
Holidays
'1/11/2015'
'2/12/2015'
'2/12/2016'
'3/13/2015'
'4/13/2015'
'5/14/2015'
'6/15/2015'
'7/16/2015'
];

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

drop table Holidays;

then you can use this expression:

NetWorkDays(StartDate,EndDate, ALL_HOLIDAYS)

[Anonymous]

Guys,

I need to add these holidays with Master calendar. Plz help...

[rahulpawarb]

Hello Siva,

It would be great if you could elaborate more in terms of current situation and expected result? This will help to reduce the turnaround time.

Thank you!

Rahul

[jonathandienst]

Based on Tim D's script, adding the Peek to get the list of holidays into a variable:

Holidays:

LOAD * INLINE [

Holidays

'1/11/2015'

'2/12/2015'

'2/12/2016'

'3/13/2015'

'4/13/2015'

'5/14/2015'

'6/15/2015'

'7/16/2015'

];

Holidays2:

Load

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

Let vHolidays = Peek('ALL_HOLIDAYS');

Drop table Holidays, Holidays2;

Then the expression should be

=NetWorkDays(StartDate,EndDate, $(vHolidays))

(use the correct date format for your system and you can replace the inline load with a load from a file or database. Make sure that the variable contains a comma separated list of  date strings in quotes).

[Anonymous]

Rahul,

Thanks for your reply, I want to pick only the hours of working days and need to exclude all the public holidays, also if any month/year selected in the master calendar the data should be shown for only those working days excluding public holidays. For this situation how can I write script, Plz explain.....

[zhadrakas]

you can add a flag to your master calender

sample:

//your Holidays

MapHolidays:

mapping LOAD Date(Date,'DD/MM/YYYY') as Date,1

FROM

Holidaylist.xls

(biff, embedded labels, table is holidays$);

add this line to your mastercalender script:

if(applymap('MapHolidays',Date)=1,1,0) as Flag_Holiday