Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a data set where some time values are presented in seconds. Data on the chart should be displayed in hh:mm:ss format.
I've used the Interval() function in the script:
Interval(time_field/86400, 'hh:mm:ss') as TIME_VALUE
and everything was looking good.
But,
I need to avoid any data transformation in my script and simply rely on data from the database.
I was hoping that using the same approach on the data side (code from the View Script):
CAST (TIME_SEC / 84600 AS NUMBER (20, 18)) AS TIME_FULL
will work the same way, but I've noticed some rounding (?) issues.
With small values in seconds, everything looks good:
With values growing, the error increases:
And the difference reaches significant values when time in seconds represent large interval (more than 2 minutes difference in ~1,5 hours range....):
I could not find any reasonable explanation for that.
I would appreciate any suggestions.
Regards,
Vladimir
It is possibly answered here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors
http://qlikviewcookbook.com/2011/10/correct-time-arithmetic/
I agree with tresesco - this is the right source. Especially have a look at Robs comment: avoid using division to convert time.
See also the link above
Hi
You want to avoid transforming the data in QV - that's fine. Interval() does not transform the data, it simply formats to hh:mm:ss (or other format that you specify) - the underlying data value is unchanged.
You can prove this to yourself by entering something like this in a text box:
=Num(Interval(3233))
Num gets the underlying value...
HTH
Jonathan
Jonathan,
Regardless the transformation definition:
Why TIME_1 and TIME_2 are significantly different?
Interval(TIME_SECONDS/86400, 'hh:mm:ss') as TIME_1, | |
Interval(TIME_DIV_BY_86400, 'hh:mm:ss') as TIME_2, |
Somehow QV know how to round the value correctly??
It's good article and nice discussion , but it's still not clear for me why QV's Interval() function works better in this case...
Interval(TIME_SECONDS/86400, 'hh:mm:ss') as TIME_1, // result is correct |
Interval(TIME_DIV_BY_86400, 'hh:mm:ss') as TIME_2, // result is wrong |
Somehow QV know how to round the value correctly??
Excel works perfectly too...
SECONDS | SECONDS/86400 | EXCEL FORMATTED |
5865 | 0.067881944444444400 | 1:37:45 |