topic Re: Interval() seconds to Num Format in QlikView

Interval() seconds to Num Format

Mon, 17 Feb 2014 22:55:43 GMT

I need to convert the difference between two time stamps (in seconds) to the Num() format so that I can perform calculations using the result.

I am using the interval function to calculate the number of seconds between two time stamps:

Interval('9:08:15 AM' - '9:07:42 AM', 'ss')

The result for the above example is 33 seconds, which is correct.

I want to be able to add a number from another field to those seconds. For example, add 5 to the result above. If I try to perform this addition, the interval value reverts to a time decimal. For example:

5 + Interval('9:08:15 AM' - '9:07:42 AM', 'ss') = 15.000381944444

The result I am looking for is 38 seconds. Using any combination of Num() and Text() has not helped.

Any ideas?

Re: Interval() seconds to Num Format

swuehl — Mon, 17 Feb 2014 22:59:04 GMT

Try Interval('9:08:15 AM' - '9:07:42 AM' + maketime(0,0,5), 'ss')

Re: Interval() seconds to Num Format

Mon, 17 Feb 2014 23:06:41 GMT

I should have added that I need to multiple the resulting seconds by a dollar amount. This helps to get the total number of seconds, but I still need the result to be in a number format to perform additional calculations.

It looks like the this might actually get the correct answer:

Num(Interval('9:08:15 AM' - '9:07:42 AM', 'ss') *24*60*60)

Re: Interval() seconds to Num Format

swuehl — Mon, 17 Feb 2014 23:19:27 GMT

Right, QV time and date values are numbers and stored as fractions of days.

Interval('9:08:15 AM' - '9:07:42 AM', 'ss') is just formatting the underlying number ( a small fraction) as text showing 33.

So you need to multiply the underlying number with 24*60*60 to get a number 'in seconds', or you could try

Num#(Interval('9:08:15 AM' - '9:07:42 AM', 'ss')

QlikView Date fields

Re: Interval() seconds to Num Format

maxgro — Mon, 17 Feb 2014 23:23:55 GMT

I think

=interval(Timestamp#('09:08:15 AM', 'hh:mm:ss tt') - Timestamp#('9:07:42 AM', 'hh:mm:ss tt') + interval#(5, 'ss'), 'ss')

Re: Interval() seconds to Num Format

Dalton_Ruer — Wed, 23 Aug 2023 12:47:03 GMT

Uggghhhh so many times in the past I struggled and couldn't understand why NUM didn't give me what I wanted. Num# is the key. Glad I found this.