topic Re: Networkdays function in QlikView

Networkdays function

josephinetedesc — Fri, 15 Jul 2016 02:00:31 GMT

Hi - to give an example of what I mean:

datestamp 1 1/1/2016 9:00 am

datestamp 2 1/1/2016 9:30 am

if I use the function

Networkdays(datestamp1, datestamp2) - the answer is 1

if I use sum(timestamp2 - timestamp1) - the answer is 0.0 something.

I have been asked to show Networking days as 0 - if the two timestamps occur on the same days. Simple formula:

Networkdays(datestamp1, datestamp2) -1

But - working on some scenarios of dates - there is a problem when timestamp2 occurs on a Saturday.

for example:

Tuesday 7/06/2016 2:00:00 PM and Saturday11/06/2016 3:00:00 PM

dateCT	dateQA	answer	Network days
Wednesday, 1 June 2016	Wednesday, 1 June 2016	0.041666667	1
Wednesday, 1 June 2016	Thursday, 2 June 2016	1.041666667	2
Tuesday, 7 June 2016	Friday, 10 June 2016	3.041666667	4
Tuesday, 7 June 2016	Saturday, 11 June 2016	4.041666667	4
Wednesday, 1 June 2016	Wednesday, 1 June 2016	0.001388889	1

(NetworkDays -1) will give 3 days which is not correct.

Will this problem only occur when timestamp2 is a weekend date?

Will I need to have another formula to account for these occassions?

Thank you

Re: Networkdays function

Fri, 15 Jul 2016 02:13:49 GMT

Hi Josephine,

Why don't you simply use

(dateQA-dateCT)+1

I don't think you need NetworkDays here.

Re: Networkdays function

josephinetedesc — Fri, 15 Jul 2016 02:23:31 GMT

Hi Ramkumar

the problem is that work does not occur on weekends (so far at leat in Australia for some industries

So we need to take this into account.

Re: Networkdays function

pavan045 — Fri, 15 Jul 2016 05:47:58 GMT

Try below expression

if(Date(Timestamp1) = Date(Timestamp2),

NetworkDays(Timestamp1, Timestamp2) - 1,

NetworkDays(Timestamp1, Timestamp2))

Hope this helps

Thank you

Re: Networkdays function

PrashantSangle — Fri, 15 Jul 2016 06:23:48 GMT

Hi,

try with simple if else

like if(floor(datestamp1)=floor(datestamp2),1,Networkdays(datestamp1, datestamp2))

Note: in comparision convert your timestamp value to date

for example

datestamp 1 1/1/2016 9:00 am convert to 1/1/2016

datestamp 2 1/1/2016 9:30 am convert to 1/1/2016

Regards,

Re: Networkdays function

josephinetedesc — Sat, 16 Jul 2016 09:37:18 GMT

Timestamp1 is to the 100th millisecond, if I could get the timestamp to the nearest second rather than the 100th of a second that might work. I tried date(Timestamp1, 'mm/DD/yyyy') as newDate but Qlikview seemed to remember the "rest" of the date - I need to check this. Whether the conversion is just "looks" or if the number is actually changed. Will check on Monday.

Max Dreamer - is there a way of keeping the timestamp to the nearest minute? In another situation I have a series of events that occur within a number of milliseconds of each other. I want to group them together if they occur within the same minute. I will start another discussion.

Re: Networkdays function

Mon, 18 Jul 2016 03:24:12 GMT

Hi Josephine,

can you please have a look at this.

How to remove weekend and holidays from your calculation

Re: Networkdays function

PrashantSangle — Mon, 18 Jul 2016 05:06:22 GMT

Hi,

in format attribute

mm denotes minutes

MM denotes Months

modify your expression and try.

Regards,

Re: Networkdays function

Duber101 — Tue, 02 Feb 2021 02:34:26 GMT

I hit the same issue when using dates.

For example :

networkdays ('04/02/2021', '05/02/2021') = 2 days (when I wanted 1 business day)

networkdays ('04/02/2021', '06/02/2021') = 2 days

So instead I used :

networkdays ('04/02/2021', ('05/02/2021' - 1)) = 1 day

networkdays ('04/02/2021', ('06/02/2021' - 1)) = 2 day