Networkdays function

[josephinetedesc]

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

Jo

[#2]

Hi Josephine,

Why don't you simply use

(dateQA-dateCT)+1

I don't think you need NetworkDays here.

[josephinetedesc]

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.

jo

[pavan045]

Try below expression

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

                     NetworkDays(Timestamp1, Timestamp2) - 1,

                              NetworkDays(Timestamp1, Timestamp2))

Hope this helps

Thank you

[PrashantSangle]

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,

[josephinetedesc]

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.

Jo

[#7]

Hi Josephine,

can you please have a look at this.

How to remove weekend and holidays from your calculation

[PrashantSangle]

Hi,

in format attribute

mm denotes minutes

MM denotes Months

modify your expression and try.

Regards,

[davidsimpf]

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