topic Re: How to exclude National Public Holidays in Link Table(For various Dates) in QlikView

How to exclude National Public Holidays in Link Table(For various Dates)

Wed, 28 Aug 2013 10:09:17 GMT

Dear All,

Below mentioned are the holidays which we have. I wanted to exclude all these holidays from my calendar. I had already excluded weekdays using NETWORKING Days function. Now these National Public holidays should also needs to be excluded. Kindly help me on this...

NO	DATE	DAY	TITLE
1	01-Jan-2013	TUESDAY	NEW YEAR DAY
2	14-Jan-2013	MONDAY	MAKARA SANKRANTI / PONGAL
3	15-Jan-2013	TUESDAY	THIRUVALLUVAR DAY
4	25-Jan-2013	FRIDAY	ID MEELAD / MILAD - UN - NIBI
5	26-Jan-2013	SATURDAY	REPUBLIC DAY
6	15-Feb-2013	FRIDAY	SREE PANJAMI/ SHRI CHOTTU RAM JAYANTI/ BASANT PANCHMI
7	26-Mar-2013	TUESDAY	HOLI FEAST / DOL JATRA
8	29-Mar-2013	FRIDAY	GOOD FRIDAY
9	11-Apr-2013	THURSDAY	CHANDRAMANA UGADI / TELUGU NEW YEAR'S DAY GUDI PADWA/SAHID DIWAS OF S. BHAGAT SINGH, RAJGURU & SUKHDEV
10	14-Apr-2013	SUNDAY	SOURA UGADI / TAMIL NEW YEAR'S DAY/ VAISAKHI
11	01-May-2013	WEDNESDAY	MAY DAY / MAHARASTHRA DAY
12	25-May-2013	SATURDAY	BUDDHA POORNIMA
13	09-Aug-2013	FRIDAY	RAMZAN / ID-UL-FITRA
14	15-Aug-2013	THURSDAY	INDEPENDENCE DAY
15	16-Aug-2013	FRIDAY	DE JURE TRANSFER DAY (PONDY)
16	21-Aug-2013	WEDNESDAY	RIG UPAKARMA/ RAKSHA BANDHAN
17	28-Aug-2013	WEDNESDAY	JANMASHTAMI/Krishna Jayanthi
18	09-Sep-2013	MONDAY	GANESH CHATURTHI / VINAYAKAR CHARURTHI
19	02-Oct-2013	WEDNESDAY	GANDHI JAYANTI
20	11-Oct-2013	FRIDAY	DURGA PUJA (Saptami)
21	13-Oct-2013	SUNDAY	MAHANAVAMI / AYUDHA POOJA / NAVMI
22	14-Oct-2013	MONDAY	VIJAYA DASAMI / DASAMI
23	16-Oct-2013	WEDNESDAY	BAKRID / ED-UL-ZOHA
24	18-Oct-2013	FRIDAY	MAHARSHI VALMIKI JAYANTI / LAKSHMI POOJA
25	01-Nov-2013	FRIDAY	RAJYOTSAVA DAY (KARNATAKA)/ HARYANA DAY
26	02-Nov-2013	SATURDAY	NARAKA CHATURDASI
27	03-Nov-2013	SUNDAY	KALI POOJA / DEEPAVALI / DEEPAVALI AMAVASYA
28	04-Nov-2013	MONDAY	PAREVA/VISWAKARMA/GOVARDHAN PUJA
29	14-Nov-2013	THURSDAY	MUHURRAM
30	25-Dec-2012	WEDNESDAY	CHRISTMAS

Re: How to exclude National Public Holidays in Link Table(For various Dates)

MayilVahanan — Wed, 28 Aug 2013 10:13:28 GMT

NetWorkDays() funciton itself, you can able to mention the holidays list also.

Re: How to exclude National Public Holidays in Link Table(For various Dates)

Wed, 28 Aug 2013 10:37:54 GMT

Hi ,

Thanks for the response could you please tell me how to exclude holidays in the networkdays????

Regards,

B.Vijayalakshmi

Re: How to exclude National Public Holidays in Link Table(For various Dates)

techvarun — Wed, 28 Aug 2013 10:39:07 GMT

Hello Vijayalakshmi

You need to create a holiday table and update all these dates there and while computing net working days using NetWorkDays() function between two days u need to execute a query to find out what is the number of holidays between 2 days and you can directly subtract from the networkdays.

Regards

Re: How to exclude National Public Holidays in Link Table(For various Dates)

techvarun — Wed, 28 Aug 2013 10:41:05 GMT

this http://community.qlik.com/thread/12770 may help u

Regards

Re: How to exclude National Public Holidays in Link Table(For various Dates)

MayilVahanan — Wed, 28 Aug 2013 10:41:56 GMT

Try like this

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

Bold dates are holidays

NetweorkDays()

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Re: How to exclude National Public Holidays in Link Table(For various Dates)

techvarun — Wed, 28 Aug 2013 10:42:07 GMT

http://community.qlik.com/thread/85602 again an easy example