topic Re: Networkdays with Datetime fields in QlikView

Networkdays with Datetime fields

nickedw0 — Wed, 25 Nov 2020 16:16:04 GMT

I need to find working days between a date field and and a DateTime field. The following results in a zero value for DaysWork Any help would be appreciated.

ProjectTable:

LOAD *, recno() as InvID INLINE [
BegDate, EndDate
03/20/18, 03/28/2018 12:00:00
11/05/2018, 12/10/2018 13:00:00
1/4/2018, 2/5/2018 09:00:00
2/10/2018, 3/31/2018 10:00:00
03/25/18, 5/19/2018 11:00:00
09/01/2018, 9/13/2018 12:30:00
09/01/218, 9/14/2018 11:30:00
09/10/2018, 9/15/2018 14:00:00
] ;

NrDays:

Load
* ,NetworkDays(BegDate,EndDate) as DaysWork
Resident ProjectTable;
Drop table ProjectTable;

Re: Networkdays with Datetime fields

sergio0592 — Thu, 08 Nov 2018 23:13:37 GMT

Hi,

Your two field date are different. It works if you load :

Load

* ,NetworkDays(Date#(BegDate,'MM/DD/YYYY'),floor(Timestamp#(EndDate,'MM/DD/YYYY hh:mm:ss'))) as DaysWork

Resident ProjectTable;

Drop table ProjectTable;

And take a look here about floor function

Floor It!

Re: Networkdays with Datetime fields

qlikviewwizard — Fri, 09 Nov 2018 01:31:33 GMT

Please try this.

ProjectTable:

LOAD

DATE(DATE#(BegDate,'MM/DD/YYYY')) as BegDate,

DATE(DATE#(EndDate,'MM/DD/YYYY h:mm:ss')) as EndDate,

recno() as InvID INLINE [

BegDate, EndDate

03/20/18, 03/28/2018 12:00:00

11/05/2018, 12/10/2018 13:00:00

1/4/2018, 2/5/2018 09:00:00

2/10/2018, 3/31/2018 10:00:00

03/25/18, 5/19/2018 11:00:00

09/01/2018, 9/13/2018 12:30:00

09/01/218, 9/14/2018 11:30:00

09/10/2018, 9/15/2018 14:00:00

] ;

NrDays:

Load

* ,NetworkDays(BegDate,EndDate) as DaysWork

Resident ProjectTable;

Drop table ProjectTable;