Solved: Round Off the number 0.1500 to 0.2 - Qlik Community

Report Inappropriate Content · ‎2017-01-20

Hi,

We have requirement to round off the value to 1 decimal places in chart.

Qlikview is not rounding off the number, if digit at first decimal place is odd and there are only zeros after second decimal place.

PFB the sample data.

I want to round off the number : 0.1500 to 0.2 only

Is there any way I can achieve this?

Thanks in advance.

rwunderlich · ‎2017-01-23

In your case, the problem is Rounding Errors as suggested by Sunny. A workaround would to add a small increment to the number. eg

sum(A4)+.00000001

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

sunny_talwar · ‎2017-01-20

This is because of Rounding Errors

m_woolf · ‎2017-01-20

It's hard to tell without seeing the data or your round expressions, but I suspect the 0.1500 data point is actually something like 0.149999.

Anonymous · ‎2017-01-20

I guess you can use Ceil(0.350,0.05). in you case Ceil(A4,0.05). you should be able to control the number decimal point from the chart.

rwunderlich · ‎2017-01-20

as mw indicated, it's probably because the underlying numbers are something like 0.1499999. When you format it with 3 decimal places, Qlik rounds it to 0.150. If you want to round using 3 decimal precision, round it twice like this:

round(round(Value,.001))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

sunny_talwar · ‎2017-01-21

That's a smart way to handle the issue. Thanks for proposing this Rob

Report Inappropriate Content · ‎2017-01-23

Hi Rob,

PFA the sample data.

Original data is 0.1500 only and I want to get it round off to 1 decimal place only.

Can you take a look.

Thanks

Report Inappropriate Content · ‎2017-01-23

Ceil function would round off all values.

for ex I don't want 0.14 to get round off to 0.2.

oknotsen · ‎2017-01-23

Could you explain why you would nest round() functions and not do it in just 1 time use of the round() function?

May you live in interesting times!

rwunderlich · ‎2017-01-23

Onno,

It's an edge case, but the reasoning is this. Suppose you have the value 1.149999. If you display that value in a chart with a format of 3 decimal places, QV will round it to 1.500. If in the same chart or another context, you use the function round(Value,1) (or an integer number format), the displayed value will be 1, which may be unexpected when the user saw the number previously as 1.5. So my "double round" suggestion was a way to introduce the consistency the OP was asking for. I wouldn't consider it a normal practice.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com