topic Re: Date duration Time Calculation in QlikView

Date duration Time Calculation

Wed, 20 May 2015 11:34:49 GMT

Dears,

any idea for how to calculate the duration between two date :

if i have date without time stamp in tables like this

Date1	Date2
1/5/2014	1/21/2014
11/10/2015	11/20/2015
6/18/2014	6/19/2014
8/17/2010	8/25/2010
8/18/2010	8/26/2010
8/19/2010	8/27/2010
8/20/2010	8/28/2010
8/21/2010	8/29/2010
8/28/2010	8/30/2010
8/29/2010	8/31/2010
8/30/2010	9/14/2010
8/31/2010	9/15/2010
9/14/2010	9/16/2010
9/15/2010	9/24/2010
9/16/2010	9/25/2010
9/17/2010	9/26/2010
9/18/2010	9/27/2010
9/19/2010	9/28/2010
9/20/2010	9/29/2010

dears i try your solutions but it is not work this is the QVD

Message was edited by: ahmad khalid

Re: Date duration Time Calculation

Anonymous — Wed, 20 May 2015 11:58:41 GMT

Use interval to get the difference b/w the date in days.

interval(date1- date2,'d') as [Duration]

Thanks

BKC

Re: Date duration Time Calculation

PrashantSangle — Wed, 20 May 2015 11:58:41 GMT

Hi,

If it is valid date then

just use num(date2)-num(date1)

gives you difference in days.

Regards

Re: Date duration Time Calculation

Clever_Anjos — Wed, 20 May 2015 11:59:09 GMT

Simple as Date2 - Date1 (number of days)

interval(Date2 - Date1) (timestamp)

Re: Date duration Time Calculation

Anonymous — Wed, 20 May 2015 12:03:43 GMT

PFA

Re: Date duration Time Calculation

Wed, 20 May 2015 12:24:30 GMT

Thank you for all but i missed important thing the two date column in different table i try to do what you say but it not work and also i tray to take the Date# and calculated but nothing appear also when i but it in variable the same because this is read from the database

Re: Date duration Time Calculation

Anonymous — Wed, 20 May 2015 12:30:22 GMT

then try this in UI .

Re: Date duration Time Calculation

Wed, 20 May 2015 12:32:36 GMT

i tray to do it but unfortunately not work .

Re: Date duration Time Calculation

ToniKautto — Wed, 20 May 2015 12:38:58 GMT

First you need to make sure the values in your table are actually dates. For example define your format variables to match your incoming format.

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

To make a interval calculation in the script, you would need to put the two fields in the same table. This can be done by joining the tables.

In the front end you can do the interval in a straight table, as already suggested with interval(date1- date2,'d')

If you can not sort it out, please provide sample QVW file so that it is possible to see where you go wrong.

Re: Date duration Time Calculation

Wed, 20 May 2015 13:24:25 GMT

In the script try the below: What I have done is based on the thoughts put forward and nothing new.

Formatting in the script can be done if you dont want to change the general date settings. also the difference is produced as days you could change that if you desire.

datesTable:

LOAD * INLINE [

Date1, Date2

1/5/2014,1/21/2014

11/10/2015,11/20/2015

6/18/2014,6/19/2014

8/17/2010,8/25/2010

8/18/2010,8/26/2010

8/19/2010,8/27/2010

8/20/2010,8/28/2010

8/21/2010,8/29/2010

8/28/2010,8/30/2010

8/29/2010,8/31/2010

8/30/2010,9/14/2010

8/31/2010,9/15/2010

9/14/2010,9/16/2010

9/15/2010,9/24/2010

9/16/2010,9/25/2010

9/17/2010,9/26/2010

9/18/2010,9/27/2010

9/19/2010,9/28/2010

9/20/2010,9/29/2010

];

DatesDifference:

load

Date#(Date1) AS StartDate,

Date#(Date2) AS EndDate,

Interval(Date#(Date2,'MM/DD/YYYY')-Date#(Date1,'MM/DD/YYYY'),'d') as datdif

resident datesTable;