Problem with round

[wunderch]

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

[#2]

How about the ceil() function?

EG. ceil( 3.88 , 0.1 ) returns 3.9

[swuehl]

=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.

[wunderch]

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

[MayilVahanan]

Hi

     Try like this,

     =Ceil(PRICE,0.01) gives 0.29

     Hope it helps

[wunderch]

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

[swuehl]

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

[mauro_villa]

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!

[JoaquinLazaro]

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!!!