topic Re: Qlikview Not Rounding a value with .9999999 in QlikView

Qlikview Not Rounding a value with .9999999

Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

So I have seen a strange occurrence in Qlikview, Qlikview is refusing to round my values ending with a recurring number i.e 34.63999999. This specific value is calculated through a calculated-dimension field (Not sure if this has an impact as to why it is not working)

I have tried the following functions on this dimension:

ROUND(value,0.01)

NUM(value, '#,##0.00')

CEIL(value, 0.1)

FLOOR(value, 0.1)

Each of the above rounds all my other values except those that end in a recurring decimal value. Is there something I am missing, or another rounding function that accommodates for this?

Thanks!

Re: Qlikview Not Rounding a value with .9999999

MK_QSL — Fri, 03 Jul 2015 08:55:06 GMT

Something like this

Aggr(ROUND(YourExpression),Dimension)

Re: Qlikview Not Rounding a value with .9999999

jagan — Fri, 03 Jul 2015 08:57:04 GMT

Hi,

The below example gives me 34.64, what is your expected value? Which version you are using?

=Round(34.63999999, 0.01)

Re: Qlikview Not Rounding a value with .9999999

Fri, 03 Jul 2015 09:06:10 GMT

I have created a calculated dimension and used Round. It worked perfectly.

=Round(D1/D2,0.01)

Is it possible for you to upload your QVW with sample data.

Regards,

KKR

Re: Qlikview Not Rounding a value with .9999999

Fri, 03 Jul 2015 09:21:21 GMT

So my calculated dimension looks like this:

=class(aggr(([Results_Difference]),[Results_Difference]),num(round(vIncrement, 0.01),'#,##0.00'),'x',num(round(vLowerLimit,0.01),'#,##0.00'))

And my result looks like this:

The values ending with .65000001 is the recurring decimal values, the NUM should be limiting it to 2 decimal values, but it does not.

Re: Qlikview Not Rounding a value with .9999999

hic — Fri, 03 Jul 2015 09:38:28 GMT

It seems to me as if you want to format the numbers rather than rounding them. Use

Num( Number, '#.00' )

instead.

Also, note that the internal binary representation cannot be rounded to exact decimal numbers. See Rounding Errors

HIC

Re: Qlikview Not Rounding a value with .9999999

marcus_sommer — Fri, 03 Jul 2015 09:40:04 GMT

Rounding of numbers has always technically limitations: Rounding Errors. As an alternatively you could try some way of cut the results with string-functions like:

left(YourAggr, index(YourAggr, '.') + 2)

also possible could be ways to split the number with floor() and frac() and multiply and/or format them and adds together again - maybe included in a dual().

- Marcus

Re: Qlikview Not Rounding a value with .9999999

Fri, 03 Jul 2015 10:53:35 GMT

Thanks Henric. I solved the rounding problem by restarting Qlikview (not sure why this worked) and then I got the result in the picture posted, but the NUM is still as issue.

I tried using the expression '#.00' instead but it still gives me the same result.

Re: Qlikview Not Rounding a value with .9999999

qlikviewwizard — Mon, 06 Jul 2015 06:59:10 GMT

Hi anekotze913,

Could you provide the sample application with the dummy data?