topic Re: To get number of hours between 2 dates in QlikView

To get number of hours between 2 dates

Mon, 14 Dec 2015 11:08:18 GMT

Hi,

I have to find the number of hours in between 2 dates i.e. u_resolved_at and opened_at.

How to find this in QlikView Chart

number	No. Of Hours	u_resolved_at	opened_at
	-	-	-
INC000013695260	-	6/4/2015 7:45:00 AM	6/1/2015 10:04:00 AM
INC000013695759	-	6/1/2015 4:55:00 PM	6/1/2015 11:31:00 AM
INC000013696272	-	6/2/2015 5:14:00 PM	6/1/2015 1:13:00 PM

Thanks..

Re: To get number of hours between 2 dates

oknotsen — Mon, 14 Dec 2015 12:01:00 GMT

In your load script, do something like this:

(opened_at - u_sesolved_at) * 24 as HoursOpen,

Re: To get number of hours between 2 dates

puttemans — Mon, 14 Dec 2015 13:30:32 GMT

Hi there,

Try the 'interval' function.

Num#(Interval (u_resolved_at - opened at, 'mm'))/60 as hours

If you need it rounded

round(Num#(Interval (u_resolved_at - opened at, 'mm'))/60 as hours, 0.1) as hours

(you need the Num# to convert the difference in a number)

Kind regards,

Johan

Re: To get number of hours between 2 dates

its_anandrjs — Mon, 14 Dec 2015 13:35:08 GMT

Try this

Interval(u_resolved_at - opened_at, 'hh')

Note:- Dates are in proper timestamp format required.

Regards

Anand

Re: To get number of hours between 2 dates

effinty2112 — Mon, 14 Dec 2015 13:53:05 GMT

Hi Sweta,

number	opened_at	u_resolved_at	Hours Difference
INC000013695260	6/1/2015 10:04:00 AM	6/4/2015 7:45:00 AM	69.68
INC000013695759	6/1/2015 11:31:00 AM	6/1/2015 4:55:00 PM	5.39
INC000013696272	6/1/2015 1:13:00 PM	6/2/2015 5:14:00 PM	28.01

The last column is this expression:

=Floor(24*(

num(Timestamp#(u_resolved_at,'M/D/YYYY HH:mm:ss TT'))

num(Timestamp#(opened_at,'M/D/YYYY HH:mm:ss TT'))

),0.01)

You can adjust the Floor() function to get the degree of accuracy you require.

Re: To get number of hours between 2 dates

Thu, 31 Dec 2015 03:24:35 GMT

Thanks.. It worked..

Re: To get number of hours between 2 dates

oknotsen — Thu, 31 Dec 2015 10:06:09 GMT

Please do not flag your own answer as Helpful, especially when it is not.

I took the liberty of removing the "Helpful" flag.

What you are supposed to do is flag the Correct Answer as Correct Answer and posts that helped you get there (so those that you consider helpful) as Helpful.

Please do the above to close the topic; thank you.