topic Re: Get Number of days excluding Friday and holidays in QlikView

Get Number of days excluding Friday and holidays

elie_issa — Tue, 31 Jan 2017 15:13:45 GMT

Dear Concerned,

I am trying to get the number of days between the two below dates "DC Order Date" and "DC Pcik Date" and exclude Fridays and holidays.

Any idea if doable using the networkdays function or i have to use another function.

Please advise.

Thanks.

ali_hijazi‌

Re: Get Number of days excluding Friday and holidays

Marcoimp — Tue, 31 Jan 2017 16:33:34 GMT

Hi,

you can build the holidays table using an autonumber generation calendar or using an existing table.

Something like this:

HolidayCalendar:

Load

//this build a string with dates in "" and comma separated style

Chr(39) & Replace(Concat(date(Holiday),','),',',chr(39) & ',' & chr(39)) & chr(39) as HolidayList

FROM

//suppose to have a table with a list of Holidays with a single field called "Holiday" (with fridays too)

Holidays.qvd(qvd)

//suppose needing only the last 2 years...

WHERE Year(Holiday)>=(year(today())-1) and Year(Holiday)<=year(today())

Let vHolidayList = Peek('HolidayList',0,'HolidayCalendar');

DROP TABLE HolidayCalendar;

Cell Formula:

=networkdays(StartDate, EndDate, $(vHolidayList))

Re: Get Number of days excluding Friday and holidays

sinanozdemir — Tue, 31 Jan 2017 19:37:44 GMT

Hi,

How about the below approach:

Since Monday is 1 and Friday is 5 in QlikView, and you want to exclude Fridays and holidays. Then we can add 1 to the begin date and deduct 1 from the end date to come up with the exact working days.

I only tested for a few date ranges.

Let me know if this works.

There was also another thread regarding to this. Although, the approach in the below thread didn't work for me.

Re: Get num of days between 2 dates

Thanks

Re: Get Number of days excluding Friday and holidays

elie_issa — Wed, 01 Feb 2017 07:59:01 GMT

Hi Marco,

Thanks for your reply.

But in this case you are excluding also saturday and Sunday.

I need to count Saturday and Sunday and exclude Friday and holidays.

Re: Get Number of days excluding Friday and holidays

hani_saghir — Tue, 07 Feb 2017 10:35:41 GMT

Hi,

create an island table for working dates with 2 fields: WK_Date and WorkingDay (1= working, 0= Weekend or Holiday), and populate your entire calendar table in it while filling the WorkingDay.

and in the expression of the date difference field, use the following:

sum(if(WK_Date >= [DC Order Date] and WK_Date <= [DC Pcik Date], WorkingDay))

this should work.