topic Re: Difference in Time only (No Timestamp) in QlikView

Difference in Time only (No Timestamp)

Thu, 11 Dec 2014 20:27:09 GMT

Need to calculate the time difference between two time considering the AM and PM factor.

My time are in this format

Start = 11:55:37 PM

End = 1:10:33 AM

I know I have to use Interval() but I am getting only negative values as the AM PM is not being considered. I also need to display the total difference in minutes.

Re: Difference in Time only (No Timestamp)

Anonymous — Thu, 11 Dec 2014 20:45:04 GMT

= interval ( time# ( '11:55:37 PM' , 'h:mm:ss tt' ) - time# ( '1:10:33 AM' , 'h:mm:ss tt' ) , 'hh:mm:ss' )

Re: Difference in Time only (No Timestamp)

maxgro — Thu, 11 Dec 2014 21:29:16 GMT

in minutes

(time#('11:55:37 PM', 'hh:mm:ss tt') - time#('1:10:33 AM', 'hh:mm:ss tt')) * 24*60

interval(time#('11:55:37 PM', 'hh:mm:ss tt') - time#('1:10:33 AM', 'hh:mm:ss tt'), 'mm')

Re: Difference in Time only (No Timestamp)

Thu, 11 Dec 2014 21:41:22 GMT

Thank you for the reply Bill but i'm still not getting the right results. Hope the below snap helps clarify my predicament:

On the first one the answer difference should have been 00:14:04 which is not the case

Re: Difference in Time only (No Timestamp)

Anonymous — Thu, 11 Dec 2014 21:47:59 GMT

Try subtracting the Start time from the End time. Looks like you maybe doing it the other way round.

Re: Difference in Time only (No Timestamp)

Peter_Cammaert — Thu, 11 Dec 2014 21:49:53 GMT

Add a date part to the underlying time value and your problem will be solved.

QlikView will consider 12:10:33 AM to be the morning of the same day, because the date value = 0. In your case, you would like to have this date part to be at least = 1...

Peter

Re: Difference in Time only (No Timestamp)

MarcoWedel — Thu, 11 Dec 2014 23:15:11 GMT

Hi,

the best way would be to subtract timestamps instead of time values (including the date part as already suggested).

If this information is not available, then you can imply that the difference has to be positive and less than 1 (day).

So one possible solution could be:

=Interval(fmod(End-Start+1,1))

given your time values are already loaded as dual values (i.e. numerical formatted as time) like your screenshot suggests.

If your time fields are simply text, then you have to convert them (again as already suggested) like:

=Interval(fmod(Time#(End,'hh:mm:ss TT')-Time#(Start,'hh:mm:ss TT')+1,1))

hope this helps

regards

Marco

Re: Difference in Time only (No Timestamp)

Thu, 11 Dec 2014 23:45:36 GMT

Marco that is exactly what I needed. Thank you so much.

Re: Difference in Time only (No Timestamp)

MarcoWedel — Fri, 12 Dec 2014 00:18:10 GMT

you're welcome

regards

Marco