topic Re: Issue with rounding in QlikView

Issue with rounding

Thu, 28 Aug 2014 01:53:33 GMT

Hi all,

I'm using Qlikview v11.20.12451.0 SR7.

I'm having some trouble with numbers getting rounded down instead of up. (eg. 0.15 rounded to 0.1 instead of 0.2)

This issue can be observed by the following steps:

1. Loading an inline value of 0.15

[Data]:

load * inline
[data
0.15];

2. Reload and then create either of the following expressions:

=num(sum(data),'#,##0.0')

=round(sum(data),0.1)

3. 0.1 will be shown instead of 0.2

4. However if '0.15' is used in place of 'sum(data)', the figure will show correctly.

Is there a bug with rounding loaded data? Currently I'm working around it by multiplying my values by 1.0000000001.

Would appreciate any help, thanks!

Re: Issue with rounding

Anonymous — Thu, 28 Aug 2014 03:53:21 GMT

please review this post

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

Re: Issue with rounding

ecolomer — Thu, 28 Aug 2014 04:10:03 GMT

The usual rounding rule is that if it is neglected figure is 5 or greater than 5, a drive to the previous position is added, if the figure is discarded is less than 5, the previous position is unchanged.

So, if what you want is that 0.15 is used as 0.1 instead of 0.2 so you should do is use is the floor function which rounds you down.

You have these options:

ceil (0.15, 0.1) = 0.2

floor (0.15, 0.1) = 0.1

round (0.15, 0.1) = 0.2

Re: Issue with rounding

Thu, 28 Aug 2014 06:04:59 GMT

Thanks jsaradhi,

from the post it seems that the hexadecimal representation results in the number not being the same as the one which was originally loaded when converted back to decimal.

Hence, 0.15 when converted to hex and then back to dec would give a value of 0.149999999999... instead of 0.15, resulting in the rounding issue.

I'm unable to solve the issue though, as I cannot convert all the numbers to integers in my application.

Re: Issue with rounding

Thu, 28 Aug 2014 06:12:42 GMT

I think you misunderstood me, what I want is the usual rounding rule.

Currently what I'm getting is: round(0.15,0.1)=0.1; which is unexpected as I wanted the result to be 0.2.

Re: Issue with rounding

CELAMBARASAN — Thu, 28 Aug 2014 06:27:33 GMT

Hi,

Looks like a bug

It works only if loaded data has more than 2 decimals

[Data]:

load data&'1' as data inline

[data

0.15

0.152];

adding a 1 at the end doesn't impact any problem with the rounding number, but it works