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: 
Not applicable

Network days with working saturday and holidays

Hi folks,

Was looking around for a good formula for working day calculation between two dates. The prolem was that we have Saturdays also as working. After going through various posts, I came across the following post

http://community.qlik.com/forums/p/22573/86229.aspx#86229

Check Rahul Fulari's explanation.

I modified it to look like this

([Sent on]-[Upload Date])-(if(Year([Sent on])-Year([Upload Date])>0,if(WeekStart([Sent on])<>WeekStart([Upload Date]),Week(yearend([Upload Date]))+(Week([Sent on])-Week([Upload Date])),0),Week([Sent on])-Week([Upload Date])))-(Networkdays([Upload Date],[Sent on])-Networkdays([Upload Date],[Sent on],$(strAllHolidays)))

([Sent on]-[Upload Date]) - The difference between the dates

((if(Year([Sent on])-Year([Upload Date])>0,if(WeekStart([Sent on])<>WeekStart([Upload Date]),Week(yearend([Upload Date]))+(Week([Sent on])-Week([Upload Date])),0)- This to get the Sundays between the days since I want to exclude them.

I needed to check the years above to offset the weeks for subsequent years. (add all the weeks of the current year to the difference in the weeks since the subsequent year will start with 1 again.

(Networkdays([Upload Date],[Sent on])-Networkdays([Upload Date],[Sent on],$(strAllHolidays))) - networkdays with and without holidays were taken into consideration just ot get the holidays. Hoilidays are defined in a variable in a script taken from an excel file.

This works great for me but would be a problem if the diference between the dates spans more than a year.

The advantage it gives me is that for some clients specific saturydays are non working (1st and 3rd). In that case, I just add them in the holiday list.

First, is this the best approach. Any better suggestions. Any thoughts on working over multiple years

5 Replies
Not applicable
Author

Hi Nimish,

Working with difference between dates I ususally turn to numbering every working day with a incremented number. If the day is non-working it gets the same number as the previous day. For example:

2010-01-14 Thursday, No=1000 (working)
2010-01-15 Friday, No=1001 (working)
2010-01-16 Saturday, No=1001 (non-working)
2010-01-17 Sunday, No=1001 (non-working)
2010-01-18 Monday, No=1001 (non-working, holiday)
2010-01-19 Tuesday, No=1002 (working)

In this way it is possible to take one date's number minus another to get the difference in working days. Add holidays to an Excel-file and check for example Saturdays and Sundays with an if() as they are always holidays (?).


SET vFirstDate = date#('2009-01-01','YYYY-MM-DD');
SET vLastDate = date#('2010-06-01','YYYY-MM-DD');

mHolidays:
mapping LOAD date#(Date,'YYYY-MM-DD') as Date,1 INLINE [
Date
2009-01-15
2009-03-18
2009-05-12
2010-01-18
];

Dates:
Load
date($(vFirstDate)+rowno()-1) as Date
autogenerate($(vLastDate)-$(vFirstDate)+1);

Dates2:
Load
Date,
rangesum(peek(DateNo),
if(match(num(weekday(Date)),5,6) // Saturdays+Sundays non-working
or applymap('mHolidays',Date,0)=1, // Lookup holiday
0, // Add zero as it is holiday
1) // Add one as it is working day
) as DateNo
resident Dates;
drop table Dates;


DateDateNo
2010-01-14268
2010-01-15269
2010-01-16269
2010-01-17269
2010-01-18269
2010-01-19270
Not applicable
Author

Dear Morgan,

You indeed have a unique perspective to the solution. But according to your solution, I would have to have some date range to start of with in the script. This might be limiting. It would be great if there is solution which is open to the start date and end date being any date in any year. Please correct me if I am wrong.

Nimish

happydays1967
Creator
Creator

In this case, would it not be better to pr-calculate at least some data in your script like the upload date - sent date etc. You can also write a custom vbscript/jscript function to make some of the calculations easier (vbscript has got a native datediff() function). Drawback of that is that those functions cannot be referenced in a chart (as far as I know ---> enhancement? Wink ). Some pre-calculation in the script will eventually make your calculations in the chart simpler and thus faster.....

Not applicable
Author

Hans,

I agree with you. My thoughts also were that baseed on Morgan's approach, within the script, I could get the minimum date and maximum dates. There are many date fields (dates trapped for different stages in the process). All of them are in different fields. But I could always get the minmum and maximum across the fields eventually within the script.

I would tend to agree that doing so within the script would make it less intesive during chart calculations, especially if a very large recordset is involved.

Nimish

Not applicable
Author

Hi Morgan,

Another thing that I thought of in your solution is how to map all the date numbers in case of multiple date fields in the same record in a table. Would you then be joining the date numbers for each date field. Would this not be too expensive to do?