topic Re: Exclude Holiday from NetWorkDays in QlikView

Exclude Holiday from NetWorkDays

Sat, 27 Sep 2014 02:25:20 GMT

Hi,

I have a very helpful expression I got from the community that returns the number of work-days since the beginning of the month:

=NetWorkDays (Date# ('9/1/2014', 'M/D/YYYY') , Today())

But it does not account for company holidays.

I do have a column called COMPANY_HOLIDAY with values Y or N.

I would like to add the COMPANY_HOLIDAY Y into the expression so it would NOT count a NetWorkDay if it is flagged as a Y in COMPANY_HOLIDAY.

I tried this, but returned nothing...

=NetWorkDays (Date# ('9/1/2014','M/D/YYYY'), COMPANY_HOLIDAY = 'Y'), Today())

I would welcome ideas.

Re: Exclude Holiday from NetWorkDays

anbu1984 — Sat, 27 Sep 2014 02:49:31 GMT

Check this

Re: Exclude Holiday from NetWorkDays

sundarakumar — Sat, 27 Sep 2014 05:30:40 GMT

PFA. I have created a variable which stores the holidays in the required format for a table. Please use the same variable as the third argument toexclude them for calculating the network days..

Hope this helps..

-Sundar

Re: Exclude Holiday from NetWorkDays

kushalthakral — Sat, 27 Sep 2014 05:55:05 GMT

Hi Jon

we have also faced the same problem and company give us list of days every month with yes and no like you

We have used simple count function to get the total working days and holidays

like count({<Cloumn>='Y'>}Days)

Regards

Kushal Thakral

Re: Exclude Holiday from NetWorkDays

Sat, 27 Sep 2014 16:15:45 GMT

Thank- you Anbu,

The formula:

=''&NetWorkingDays (Date#('9/1/2014','M/D/YYYY'),Today(),$(=Concat(chr(39)&If(COMPANY_HOLIDAY='Y',Date)&Chr(39),',')))

Works just fine. It returns an integer ( the number of working days from the beginning of the month excluding weekends and holidays) that I can use in subsequent expressions. Thanks again,