Number formatting issue with decimal values

[madhudinesh]

Hi All,

I have 2 values in my pie chart which is '8.65%' and '40.65%'. When i changed the number formatting to display 1 digit value post decimal point and display as percentage, i am getting the results as '8.7%' and '40.6%'.

Note: On exporting the pie values to excel the value '40.6%' is displayed as '40.7%'

I tried to format the value '40.65' in a text box with function 'Num('40.65','##.#')' result is still '40.6' but '8.65' is displayed as '8.7'.

I tried formatting random numbers with decimal value '.65' and got to know the values till 15 are getting formatted correctly i.e 15.7 but values greater than 15 are displaying the decimal values as '16.6' or '30.6'.

Request your help to understand why there is difference in number formatting for the same decimal values.

[vamsee · Accepted solution]

Hello, 

Changing Number formatting on the Number tab/Num() does not actually round the values instead, it would do it for display purposes of the chart. When you export the chart the actual values are retained. 

Alternatively, you can Round() instead of fixing it Number tab.

Refer: 

https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

https://community.qlik.com/t5/QlikView-App-Development/Rounding-off-to-an-integer-using-NUM/td-p/115...

[madhudinesh]

Hi Vamsee,

Thanks for your reply.

I want to display the number with 1 decimal point eg. 15.65 as 15.7 and 40.65 as 40.7.

Using the Round function with offset .1 also gives me the same result. 

Syntax - Round(15.65,0.1) results in '15.7 where as Round(40.65,0.1) results in '40.6'.

Please suggest on this

[tresesco · Accepted solution]

This is a pitfall of floating point number in digital world. Since you are asking for 'why', have a look here:

https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

https://support.qlik.com/articles/000005572