topic Re: Networkdays with saturday in QlikView

Networkdays with saturday

Anonymous — Fri, 01 Feb 2013 14:42:37 GMT

Hello,

I have to count how many working days there are between two dates, but I have to consider that Saturday is ALWAYS a working day, so I cant's use the networkingdays() function, which excludes Saturday. At the same time, I can't use a calendar because I have to use the calculation for many occurrences in various years, so it should be too difficult for me to load a so long calendars.

Can anyone suggest an idea to me?

Thank you for interesting.

Re: Networkdays with saturday

Gysbert_Wassenaar — Fri, 01 Feb 2013 14:59:39 GMT

You could add a new field in the script: if(weekday(MyDate)<>6,1,0) as WorkDay. You can then sum the workday field to get the number of workdays. If you don't want to create a new field you can use the if statement in an expression in your chart.

Re: Networkdays with saturday

Anonymous — Fri, 01 Feb 2013 15:08:05 GMT

Sorry but I didn't understand.

This is the part of my script when I apply the function:

LOAD

Date(StartDate,'DD/MM/YY') As StartDateFormatted,

Date(EndDate,'DD/MM/YY') As EndDateFormatted,

networkdays (DataInizio, DataFine) As WorkingDays;

SQL SELECT

IdPersona,

StartDate,

EndDate,

FROM Table;

How can I apply your suggestion?

Thanks

Re: Networkdays with saturday

Gysbert_Wassenaar — Fri, 01 Feb 2013 15:42:27 GMT

Ah, ehm, you can't. Not without using a calendar, which you don't want to do. You could try adding a vbscript function in the macro editor:

Function GetWorkDays(StartDate, EndDate)

dCount = 0

For dt = StartDate To EndDate

If WeekDay(dt) <> 1 Then

dCount = dCount + 1

End If

GetWorkDays = dCount

End Function

And use that function in the load script. Make sure the dates that are passed to the function are numeric dates.

GetWorkDays(StartDate,EndDate) as WorkingDays

Re: Networkdays with saturday

Anonymous — Fri, 01 Feb 2013 15:53:55 GMT

Great!!

Thank you very much.

Re: Networkdays with saturday

rbecher — Fri, 01 Feb 2013 16:46:03 GMT

Hi N.

since there is no function for script (except Marco) I would prefer a calculation in script:

Load

floor(((Date2 - Date1)+1)/7)*6 + mod((Date2 - Date1)+1,7)

+ if(Weekday(Date1) + mod((Date2 - Date1)+1,7) < 6, 0, -1) as Workingdays_6

...

Please see atached example. Hope I have covered all variations. Maybe there is a possibility of simplification ..

- Ralf

Re: Networkdays with saturday

Anonymous — Fri, 01 Feb 2013 17:03:17 GMT

Hello,

Thank you for your solution, but it dosn't work properly:

12/12/12 - 22/12/12 Working days: 10 Your function: 9

17/12/12 - 22/12/12 Working days: 6 Your function: 5

20/12/12 - 22/12/12 Working days: 3 Your function: 2

Have a nice WE.

Re: Networkdays with saturday

rbecher — Fri, 01 Feb 2013 17:08:23 GMT

Small mistake..

Load

floor(((Date2 - Date1)+1)/7)*6 + mod((Date2 - Date1)+1,7)

+ if(Weekday(Date1) + mod((Date2 - Date1)+1,7) < 7, 0, -1) as Workingdays_6

...

Have a good one!

- Ralf

Re: Networkdays with saturday

Anonymous — Fri, 01 Feb 2013 17:23:28 GMT

Yeah !!

My best compliments for your ability with math! You're a sort of genius with the numbers...

Thank to both of you.

Re: Networkdays with saturday

rbecher — Fri, 01 Feb 2013 17:36:43 GMT

Thx, this is just basic stuff. I'm not that math geek 😉

But a good exercise...

Re: Networkdays with saturday

acorral312 — Wed, 12 Apr 2017 09:59:50 GMT

Hi Ralf,

I would like to use your solution to find the number of working days remaining in the month (Monday-Saturday). Is there a way to exclude a list of holidays?

Re: Networkdays with saturday

ashishpalkar — Fri, 08 Dec 2017 14:57:35 GMT

Hi There

How can we get Networkdays without Saturday or Sunday?

Thanks in advance,

Re: Networkdays with saturday

rbecher — Fri, 08 Dec 2017 17:05:10 GMT

networkdays - script and chart function ‒ QlikView

Re: Networkdays with saturday

suhaskharat — Thu, 20 Sep 2018 12:43:55 GMT

how can we do to exclude 2nd and 4th Saturday and all Sundays of all months from date range

Re: Networkdays with saturday

EQ — Fri, 01 Mar 2019 03:40:50 GMT

Hi Ralf,

It is very useful but it seems not fully fit for my case.

Could you please provide how to calculate how many Saturdays or Sundays between two dates?

BR,

Eric

Re: Networkdays with saturday

smgt90 — Wed, 02 Dec 2020 02:59:00 GMT

Can you help me with a similar formula that I could use as an alternative to the LastWorkDate formula? I need it to count Saturdays as working days.