Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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))
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
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.
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.