How to calculate difference using single date field

[sirivis24s]

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

[Taoufiq_Zarra]

@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

[sirivis24s]

Hi Taoufiq,

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

[sirivis24s]

Based on AU_Entity_ID 

[sirivis24s]

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

[Taoufiq_Zarra]

@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:

[sirivis24s]

Hello Taoufiq,

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

please help me on this

[Taoufiq_Zarra]

@sirivis24s 

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

[sirivis24s]

Hello Taoufiq

You can apply at Audit Log Tab

Thanks

sujan.s

[sirivis24s]

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;