topic Re: Holidays exclusion in QlikView

Holidays exclusion

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Holidays exclusion

tresB — Mon, 30 Jan 2017 06:51:43 GMT

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/networkdays.htm

Re: Holidays exclusion

sdmech81 — Mon, 30 Jan 2017 06:55:54 GMT

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

Re: Holidays exclusion

rahulpawarb — Mon, 30 Jan 2017 07:14:06 GMT

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

Re: Holidays exclusion

zhadrakas — Mon, 30 Jan 2017 07:20:28 GMT

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)

Re: Holidays exclusion

Anonymous — Mon, 30 Jan 2017 13:54:44 GMT

Guys,

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

Re: Holidays exclusion

rahulpawarb — Tue, 31 Jan 2017 04:50:27 GMT

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

Re: Holidays exclusion

jonathandienst — Tue, 31 Jan 2017 05:07:33 GMT

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

Re: Holidays exclusion

Anonymous — Tue, 31 Jan 2017 09:16:34 GMT

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

Re: Holidays exclusion

zhadrakas — Tue, 31 Jan 2017 09:27:28 GMT

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

Re: Holidays exclusion

rahulpawarb — Tue, 31 Jan 2017 09:48:23 GMT

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

Re: Holidays exclusion

Anonymous — Tue, 31 Jan 2017 10:20:41 GMT

Thank you so much guys...

Re: Holidays exclusion

David_K — Thu, 19 May 2022 16:03:15 GMT

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