Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
elie_issa
Creator II
Creator II

Get Number of days excluding Friday and holidays

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.

Untitled.png

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

Please advise.

Thanks.

ali_hijazi

1 Solution

Accepted Solutions
hani_saghir
Contributor III
Contributor III

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.

Capture.JPG

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.

View solution in original post

4 Replies
Marcoimp
Partner - Creator III
Partner - Creator III

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





M.Imperiale
sinanozdemir
Specialist III
Specialist III

Hi,

How about the below approach:

Capture.PNG

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

elie_issa
Creator II
Creator II
Author

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.

hani_saghir
Contributor III
Contributor III

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.

Capture.JPG

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.