This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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.
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.
View solution in original post
you can build the holidays table using an autonumber generation calendar or using an existing table.
Something like this:
//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
//suppose to have a table with a list of Holidays with a single field called "Holiday" (with fridays too)
//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;
=networkdays(StartDate, EndDate, $(vHolidayList))
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 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.