topic Duration between two Time fields not dates in App Development

Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:03:30 GMT

Dear team,

I have a table where I have arrival time in one column and left time in another field, i need to know the duration between those two times

I am using qlik sense

the outcome should be in hours but decimal example 2.5 is two hrs and 30 min

thank you in advance.

Re: Duration between two Time fields not dates

benoitgochel — Wed, 25 Jul 2018 14:07:38 GMT

Hi,

You can try a Timestamp([Left Time] - [Arrival Time]) and use the format you want.

You can also chose to display as a duration in the measure parameters

Hope that helps.

Best regards

Re: Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:16:34 GMT

Thank you for your prompt reply, but it didn't work.

it is just time filed, could be not recognized as such in qlik?

example currently have them as 18:40 in an excel column

Re: Duration between two Time fields not dates

benoitgochel — Wed, 25 Jul 2018 14:19:10 GMT

What if you try something like this :

round(([Left Time]-[Arrival Time])*1440,0.02)

Re: Duration between two Time fields not dates

YoussefBelloum — Wed, 25 Jul 2018 14:21:07 GMT

Hi,

you will need to use Interval() function

Take a look here to understand how it works: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FormattingFunctions/Interval.htm

example:

Interval(left_time-arrival_time,'hh:mm')

Re: Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:21:53 GMT

no also not working.

Re: Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:25:10 GMT

Thank you

but didn't work i'm using this:

=Interval([Left Time]-[Arrival Time],'hh:mm')

Re: Duration between two Time fields not dates

YoussefBelloum — Wed, 25 Jul 2018 14:41:38 GMT

then you will need to evaluate your timestamp fields correctly using Timestamp#()

like this

=Interval(Timestamp#(left_time,'your format')-Timestamp#(arrival_time,'your_format'),'hh:mm')

PS: if the format of your time field is this: DD/MM/YYYY hh:mm:ss then just replace it in the greyed part above

Re: Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:53:54 GMT

at least i got a result but it was worng, what i am missing here is what i was using:

=Interval(Timestamp#([Left Time],'hh:mm')-Timestamp#([Arrival Time],'hh:mm'),'hh:mm')

Re: Duration between two Time fields not dates

Anonymous — Wed, 25 Jul 2018 14:56:58 GMT

hey i did it

this one works: so i was missing the timestamp

=(Timestamp#([Left Time],'hh:mm')-Timestamp#([Arrival Time],'hh:mm'))*1440

Re: Duration between two Time fields not dates

YoussefBelloum — Wed, 25 Jul 2018 15:04:29 GMT

Good, I just recommend using Interval() for this specific need, because it is designed exactly for this type of calculations..

don't forget to close the thread

Re: Duration between two Time fields not dates

Anonymous — Thu, 26 Jul 2018 07:14:18 GMT

didn't work for intervals, can you using what i shared, convert it by using intervals so i can check it.

Re: Duration between two Time fields not dates

YoussefBelloum — Thu, 26 Jul 2018 07:19:42 GMT

yes, but you will need to share a sample app for that I should take YOUR expression with YOUR data to convert it

Re: Duration between two Time fields not dates

Anonymous — Thu, 26 Jul 2018 08:11:16 GMT

Thank you here is the sample data,

i have faced an issue, that the left time sometimes is in the next day after 12:00am so the time is negative.

in the sample there is the date as well, so can you create something that will calculate the duration using the date and time,

really appreciate it.

Re: Duration between two Time fields not dates

YoussefBelloum — Thu, 26 Jul 2018 10:05:15 GMT

here you are

it is working as expected

Re: Duration between two Time fields not dates

Anonymous — Thu, 26 Jul 2018 11:19:15 GMT

it is an empty file, couldn't load it, can you paste the code here that you have used?

Re: Duration between two Time fields not dates

YoussefBelloum — Thu, 26 Jul 2018 11:51:29 GMT

try it again

there is no code, only this expression:

=Interval(Timestamp#([Left Time],'hh:mm')-Timestamp#([Arrival Time],'hh:mm'),'hh:mm')

put your dimensions and use the expression above

Re: Duration between two Time fields not dates

Anonymous — Thu, 26 Jul 2018 12:10:32 GMT

it is giving decimal and not working multiplying by 1440

and you didn't react towards the day. as subtracting two times might give negative if in different days.

regards.

Re: Duration between two Time fields not dates

YoussefBelloum — Thu, 26 Jul 2018 12:23:27 GMT

I wrote something related to the day and I delete it, if you want to include the day, you can do it by concatenating your Day field with your time field inside the Timestamp#() function, like this:

=Interval(Timestamp#([Left Date]&[Left Time],'DD-MM-YYYY hh:mm')-Timestamp#([Arrival Date]&[Arrival Time],'DD-MM-YYYY hh:mm'),'hh:mm')

if this don't solve your problem, take the solution that is working for you

Regards