topic Re: How to calculate difference using single date field in QlikView

How to calculate difference using single date field

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

Hi All,

I am New to QlikView I have data like

Change date	changer	Old assigned to	New Assigned to	Old status	New Status	Name	DTAT
11/1/2020	Sujan		Darrielle		New	-	-
11/4/2020	Darrielle	Darrielle	Sujan	New	Need More info	Darrielle	3
11/4/2020	Sujan	Sujan	Darrielle	Need More info	Open	Sujan	1
11/4/2020	Darrielle	Darrielle	Dhruv	Open	Open	Darrielle	1
11/12/2020	Dhruv	Dhruv	Dhruv	Open	Fixed	Dhruv	7
11/15/2020	Dhruv	Dhruv	Sujan	Fixed	Ready to retest	Dhruv	2
11/21/2020	Sujan	Sujan		Ready to retest	Closed	Sujan	5

I need to Calculate no of days between dates for eg

changed date is 11/1/2020 to 11/4/2020 then DTAT is 3 excluding Saturday's and Sundays .below is criteria

Thanks

Sujan.s

Re: How to calculate difference using single date field

Taoufiq_Zarra — Thu, 05 Nov 2020 09:52:58 GMT

@sirivis24s you can use networkdays function

networkdays (start_date, end_date )

for example

=networkdays (Date#('11/1/2020','M/D/YYYY') , Date#('11/4/2020','M/D/YYYY'))

output is 3

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 09:59:06 GMT

Hi Taoufiq,

How can I split Change date into multiple dates i.e, start date and enddate

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 10:00:47 GMT

Based on AU_Entity_ID

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 10:08:35 GMT

Hi Taoufiq

There are multiple Au_entityID for eg i am showing single entity

based on above screen shot how can calculate start date and enddate in network days

Re: How to calculate difference using single date field

Taoufiq_Zarra — Thu, 05 Nov 2020 10:25:03 GMT

@sirivis24s in Script you can use for example :

load *,networkdays(Date#(peek([Change date]),'M/D/YYYY'),Date#([Change date],'M/D/YYYY')) as DTAT; LOAD * INLINE [ Change date, changer,Old assigned to , New Assigned to, Old status, New Status, Name 11/1/2020, Sujan, , Darrielle, , New 11/4/2020, Darrielle, Darrielle, Sujan, New, Need More info, Darrielle 11/4/2020, Sujan, Sujan, Darrielle, Need More info, Open, Sujan 11/4/2020, Darrielle, Darrielle, Dhruv, Open, Open, Darrielle 11/12/2020, Dhruv, Dhruv, Dhruv, Open, Fixed, Dhruv 11/15/2020, Dhruv, Dhruv, Sujan, Fixed, Ready to retest, Dhruv 11/21/2020, Sujan, Sujan, , Ready to retest, Closed, Sujan ];

output:

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 10:44:21 GMT

Hello Taoufiq,

I have tried but it's not working i have attached sample QVW file and Datafile

please help me on this

Re: How to calculate difference using single date field

Taoufiq_Zarra — Thu, 05 Nov 2020 11:13:03 GMT

@sirivis24s

there is a lot of data source in the qlik file
can you specify the part where you want to apply the function

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 11:30:05 GMT

Hello Taoufiq

You can apply at Audit Log Tab

Thanks

sujan.s

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 11:35:42 GMT

Hello,

I have used below forumla but it's not working in audit log tab i have taken staright table with expression

count(DTAT)

AUDIT_LOG1:
load *,network days(Date#(peek("AU_DATE"),'M/D/YYYY'),Date#("AU_DATE",'M/D/YYYY')) as DTAT
Resident AUDIT_LOG;
Drop Table AUDIT_LOG;

Re: How to calculate difference using single date field

Taoufiq_Zarra — Thu, 05 Nov 2020 11:48:05 GMT

@sirivis24s I used from the Excel file this expression:

SampleData: load *,networkdays(Date(peek(AU_DATE)),Date(AU_DATE)) as DTAT; LOAD AU_ENTITY_ID, AU_USER, AU_DATE, AP_OLD_VALUE, AP_NEW_VALUE//, // DTAT FROM .\Defects_History.xls (biff, embedded labels, table is Sheet1$);

and this is the output :

Make sure that :

SET DateFormat='M/D/YYYY';

Re: How to calculate difference using single date field

sirivis24s — Thu, 05 Nov 2020 12:36:54 GMT

Hello Taoufiq,

I have tried it but notworking can you try this excel in your machine