topic Re: Difference in times than span midnight in QlikView

Difference in times than span midnight

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hey all

Can anyone help with what is probably a fairly simple question to answer.

I need to work out the difference in times that span midnight without them returning a huge negative number.

Time 1 = 23:59:57

Time 2 = 00:00:18

I want the answer 00:00:21 but I'm getting the answer -23:59:39.

Many thanks in advance!

sunny_talwar — Wed, 27 Sep 2017 08:34:43 GMT

May be use timestamp (which includes the date) to find the difference instead of just using the Time? Or this

Interval(If(Time2 - Time1 > 0, Time2-Time1, 1+Time2-Time1), 'hh:mm:ss')

Anil_Babu_Samineni — Wed, 27 Sep 2017 08:35:54 GMT

This?

Date(If(Time2 - Time1 > 0, Time2-Time1, Avg(1)+Time2-Time1), 'hh:mm:ss')

Anil_Babu_Samineni — Wed, 27 Sep 2017 08:37:01 GMT

Sunny, Does Date returns same as Interval ??

Anonymous — Wed, 27 Sep 2017 08:43:00 GMT

Perfect! Adding the 1 to the calculation worked perfectly! Thanks!