topic Re: Calculate difference between two dates? in QlikView

Calculate difference between two dates?

Wed, 04 May 2016 14:06:27 GMT

HI all,

I have two date fields, I have to calculate the difference in hours. viz., open date and close date.

Open_Date Close_Date

10/01/2016 08:20:00 12/01/2016 9:50:00

I used timestamp for both the columns and on the result also.

Eg: timestamp(Close_Date) - timestamp(Open_Date ) as Cycletime

Now i am getting the time in hours and minutes as below.

Eg: difference between abve dates is 49:30:00.

What I am expecting is like 49.50 hours(I have to calculate that 30 minutes into hours then add that to the 49 hours)

Thanks for any ideas.

regards

Prashanth

Re: Calculate difference between two dates?

Kushal_Chawda — Wed, 04 May 2016 14:12:43 GMT

=round( (frac(timestamp(closedate))- frac(timestamp(opendate)))*24,0.01)

Re: Calculate difference between two dates?

swuehl — Wed, 04 May 2016 14:23:06 GMT

As far as I can see,

Round( (Close_Date - Open_Date)*24, 0.01) AS CycleTime

should be enough

Re: Calculate difference between two dates?

Anonymous — Wed, 04 May 2016 14:25:50 GMT

timestamp(close date-opendate) as difference time

Re: Calculate difference between two dates?

Wed, 04 May 2016 15:34:33 GMT

Hi sweuhl,

Is the result is in minutes or hours.. I am getting wrong values??

Re: Calculate difference between two dates?

Wed, 04 May 2016 15:40:03 GMT

Hi kushal,

negative values are getting when i use the above expression..?

Re: Calculate difference between two dates?

Wed, 04 May 2016 15:42:34 GMT

Sweuhl , am I clear with my requirement ??

Re: Calculate difference between two dates?

Anonymous — Wed, 04 May 2016 16:00:36 GMT

Could this work?

Add a variable Close_Date and Open_Date in script

(DayNumberOfYear(Close_Date) - DayNumberOfYear((Open_Date)))

And to get in hours:

(interval(num(Close_Date)-num(Open_Date),'hh'))

Re: Calculate difference between two dates?

swuehl — Wed, 04 May 2016 16:46:34 GMT

Yes. CycleTime should be in hours, with a fraction part as requested (0.5 equals 30 mins):

SET TIMESTAMPFORMAT = 'DD/MM/YYYY hh:mm:ss';

LOAD *,

Round( (Close_Date - Open_Date)*24, 0.01) AS CycleTime,

timestamp(Close_Date - Open_Date) as differencetime;

LOAD * INLINE [

Open_Date, Close_Date

10/01/2016 08:20:00, 12/01/2016 9:50:00

];

Can't see that the suggested expression you've chosen as correct answer delivers 49.50, mine expression does:

Open_Date	Close_Date	CycleTime	differencetime
10/01/2016 08:20:00	12/01/2016 9:50:00	49.50	01/01/1900 01:30:00

Re: Calculate difference between two dates?

Kushal_Chawda — Wed, 04 May 2016 17:07:35 GMT

I don't understand how this could be a correct answer

Re: Calculate difference between two dates?

Wed, 04 May 2016 18:40:27 GMT

I have used timestamp on two date columns and then subtracted, on result field again applied timstamp,

Its working.

Re: Calculate difference between two dates?

Wed, 04 May 2016 18:41:22 GMT

working when i appiled the timestamp separately on the date columns.