topic Re: Round Off the number 0.1500 to 0.2 in QlikView

Round Off the number 0.1500 to 0.2

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Round Off the number 0.1500 to 0.2

sunny_talwar — Fri, 20 Jan 2017 16:28:59 GMT

This is because of Rounding Errors

Re: Round Off the number 0.1500 to 0.2

m_woolf — Fri, 20 Jan 2017 16:31:34 GMT

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.

Re: Round Off the number 0.1500 to 0.2

Anonymous — Fri, 20 Jan 2017 16:39:00 GMT

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.

Re: Round Off the number 0.1500 to 0.2

rwunderlich — Fri, 20 Jan 2017 17:33:51 GMT

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

Re: Round Off the number 0.1500 to 0.2

sunny_talwar — Sat, 21 Jan 2017 15:27:02 GMT

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

Re: Round Off the number 0.1500 to 0.2

Mon, 23 Jan 2017 05:30:07 GMT

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

Re: Round Off the number 0.1500 to 0.2

Mon, 23 Jan 2017 05:33:10 GMT

Ceil function would round off all values.

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

Re: Round Off the number 0.1500 to 0.2

oknotsen — Mon, 23 Jan 2017 09:42:17 GMT

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

Re: Round Off the number 0.1500 to 0.2

rwunderlich — Mon, 23 Jan 2017 12:16:08 GMT

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

Re: Round Off the number 0.1500 to 0.2

oknotsen — Mon, 23 Jan 2017 12:20:25 GMT

Thank you, Rob.

Re: Round Off the number 0.1500 to 0.2

rwunderlich — Mon, 23 Jan 2017 12:49:44 GMT

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

Re: Round Off the number 0.1500 to 0.2

Tue, 24 Jan 2017 05:16:59 GMT

This will help, Thanks.

Re: Round Off the number 0.1500 to 0.2

Tue, 31 Jan 2017 05:33:32 GMT

Hi All,

Below solution is also working out for all scenarios, but I am unable to deduce the exact functionality of offset and step here. Can anyone help me on that.

round(num#(0.1100),0.1,0.5) gives 0.1

round(num#(0.1400),0.1,0.5) gives 0.1

round(num#(0.1500),0.1,0.5) gives 0.2

round(num#(0.1600),0.1,0.5) gives 0.2

Thanks

Richa