topic Re: time difference between excel and qlikview in QlikView

time difference between excel and qlikview

Wed, 02 Mar 2016 06:43:49 GMT

Hello All,

When I convert 00:03:16 in excel I get the answer 0,00252099135574689 but when I convert the same 00:03:16 in qlikview I get 0,0017707329195295

I need the figure to be 0,00252099135574689 (like in the excel).

Any idea please?

Kind Regards

Hasvine

Re: time difference between excel and qlikview

ahaahaaha — Wed, 02 Mar 2016 08:34:10 GMT

Hello, Hasvine.

In the attached files, your data is modeled in the Excel and QlikView. As can be seen, the conversion results are the same in both cases.

Given that the time change in seconds, the iteration step is 1/86400 = 0.000011574 (60 seconds * 60 hours * 24 minutes = 86,400 seconds in a day). Consequently, distortion may occur if the required number is displayed in the time format .00252099135574689.

Alternatively, it is possible to use in the calculation of time in numeric format, adding a fraction of a second later when the conversion results in the time format.

Kind Regards

Andrey

Re: time difference between excel and qlikview

Wed, 02 Mar 2016 08:38:56 GMT

Hi Hasvine ,

Have a nice day.

What is the conversion function your are using.

Re: time difference between excel and qlikview

Anonymous — Wed, 02 Mar 2016 08:40:36 GMT

Which Excel function gives you the output?

Re: time difference between excel and qlikview

PrashantSangle — Wed, 02 Mar 2016 08:43:39 GMT

Hi,

use frac()

try below

=frac(time#('00:03:16','hh:mm:ss'))

Regards

Re: time difference between excel and qlikview

Peter_Cammaert — Wed, 02 Mar 2016 09:04:37 GMT

Both are incorrect.

00:03:16 is equal to 3 * 60 + 16 = 196 seconds. To get the numerical time value, you calculate the part of the day by dividing 196 / 86400 = 0,0022685185185185.

Both QlikView and Excel use the same internal representation for datetime values. I did use neither one. Just Windows calculator.

BTW when entering expression =time(0.0022685185185185) in a QlikView text box, it returns 00:03:16.

When entering 0,0022685185185185 in an Excel cell and changing the cell format to Time, it returns ... 00:03:16.

Best,

Peter

Re: time difference between excel and qlikview

Wed, 02 Mar 2016 11:32:41 GMT

thank you Peter for your explanation.

Kind Regards,

Hasvine