topic Re: Problem with round in QlikView

Problem with round

wunderch — Wed, 01 Aug 2012 12:16:45 GMT

Hi,

I have the following problem:

I have a field PRICE with "0,285" and I want to round it into "0,29"

So I take round(PRICE, '0,01') but the result is "0,28".

What's the problem??

Regards

Chris

Re: Problem with round

Wed, 01 Aug 2012 12:24:12 GMT

How about the ceil() function?

EG. ceil( 3.88 , 0.1 ) returns 3.9

Re: Problem with round

swuehl — Wed, 01 Aug 2012 12:31:31 GMT

=round(0.285, 0.01)

results in 0.29 on my side. Are you really using a string (note the single quotes) as second argument? I don't think that works.

Re: Problem with round

wunderch — Wed, 01 Aug 2012 12:54:20 GMT

Yes, when I take a textbox with formula

=round(0.285, 0.01)

then I get 0,29

But when I take the formula in the script

round(PRICE, 0.01) as PRICE,

I get 0,28

Re: Problem with round

MayilVahanan — Wed, 01 Aug 2012 13:14:39 GMT

Try like this,

=Ceil(PRICE,0.01) gives 0.29

Hope it helps

Re: Problem with round

wunderch — Wed, 01 Aug 2012 13:21:58 GMT

Thanks for your answers,

but my problem was that the field "PRICE" have many prices and that prices I want to round commercially, so I can't take ceil()

Now I have found the solution:

First I made round(PRICE, 0.001) as PRICE and then again round(PRICE, 0.01) as PRICE.

So I get 0,29 for 0,285.

Have a nice day!

Chris

Re: Problem with round

swuehl — Wed, 01 Aug 2012 13:51:25 GMT

That seems to work for 0.285, but I got the same issue with other floating points, like 0.295

I assume that's due to floating point representation issue in QV (though I think it's strange that

LOAD *, round(Price,0.01) as test1, round(0.285,0.01) as test2

INLINE [

Price

0.285

];

returns different values for test1 and test2).

I think this should work for all numbers (at least with my test data, it works):

num(round(Price*1000,10)/1000,'#.00') as PriceRounded

Re: Problem with round

mauro_villa — Wed, 12 Dec 2012 14:18:10 GMT

I have same problem and it seems that Qlikview doesn’t manage periodic number correctly in rounding and in some others operations.

When the result of a division is a periodic number, Qlik does not recognize it as periodic. Probably the result of your division is a number like 0.2849999999.. (9 periodic) that in math is the same of 0,285. Computer cannot store periodic number and so the number is saved like 0.2849999999998. QV doesn’t see this number as periodic and so the round(0.001) is “correctly” 0.28 because it is < 0.285.

Another examples is fmod(1,0.003) =0,00099999999999998. It should be 0.00099999..=0.001.

fmod(1,0.03) =0,01 works well.

I don’t know if it is a bug or not ,but often you can fix it using two round functions such as round(round(a,0.000001),0.01).

Good luck!

Re: Problem with round

Joaquin_Lazaro — Fri, 19 Apr 2013 14:30:38 GMT

Hi:

There is a problem with Round and Floor, both not working as expected.

See Support case 00170986 & 00170987.

I could write a little trick but of course have other problems behind!!!