topic Re: rounding down instead of up in QlikView

rounding down instead of up

Mon, 30 Mar 2015 13:34:32 GMT

Hi,

Can someone explain how to stop chart rounding down incorrectly?

Ther raw data which the chart is referring to shows average to be 7.85; however, on the chart when we set to 1 decimal is rounding down to 7.8 when it should round up to 7.9.

Please help.

Chris

Re: rounding down instead of up

MK_QSL — Mon, 30 Mar 2015 13:36:14 GMT

=NUM(YourExpression,'#0.0')

Re: rounding down instead of up

linusblomberg — Mon, 30 Mar 2015 13:36:43 GMT

Can you use floor() or ceil()? or round()

Re: rounding down instead of up

tcullinane — Mon, 30 Mar 2015 13:38:23 GMT

set it to 3 or 4 places and what average is returned?

Re: rounding down instead of up

buzzy996 — Mon, 30 Mar 2015 13:39:50 GMT

floor( x [ , base [ , offset ]] )

Rounding of x downwards to the nearest multiple of base with an

offset of offset. The result is a number.

Examples:

floor( 2.4 ) returns 2

floor( 2.6 ) returns 2

floor( 3.88 , 0.1 ) returns 3.8

floor( 3.88 , 5 ) returns 0

floor( 1.1 , 1 , 0.5 ) returns 0.5

Re: rounding down instead of up

Mon, 30 Mar 2015 13:40:50 GMT

7.8500

Re: rounding down instead of up

Mon, 30 Mar 2015 13:43:27 GMT

this could be quite an issue as we use averages a lot to score satisfaction. Is there not a general setting? and have others not come across this issue?

Chris

Re: rounding down instead of up

avinashelite — Mon, 30 Mar 2015 13:43:43 GMT

use floor(feildname) this will do

Re: rounding down instead of up

maxgro — Mon, 30 Mar 2015 13:45:39 GMT

=round(7.85 * 10/10, 0.1) 7.9

=round(7.85, 0.1) 7.8

Re: rounding down instead of up

Anonymous — Mon, 30 Mar 2015 13:49:37 GMT

Chris

This blog by hic explains the in & outs of rounding errors with binary floating point numbers:

Rounding Errors

Regards, Bill

Re: rounding down instead of up

Mon, 30 Mar 2015 13:52:06 GMT

current expression is

Avg

([PI2. Overall satisfaction with this service area])

so does this become:

Floor(Avg([PI2. Overall satisfaction with this service area]))

Re: rounding down instead of up

Mon, 30 Mar 2015 13:59:51 GMT

Hi Bill,

Thanks for this but I will have to be honest and say I dont fully understand it.

For the issue I'm having we have question which our customers rate us on from 1-10. The 20 records we have bring an average of 7.85 when you selct the data in excel. I dont understand why in Qlikview when we have the same calcuation ([PI2. Overall satisfaction with this service area]) it produces a result of 7.8 when 1 decimal but more crucially how we get solve it both in this instance and how we can avoid in the future?

Chris

Re: rounding down instead of up

Anonymous — Mon, 30 Mar 2015 14:14:12 GMT

Chris

Have a look at this thread. The "solution" is a bit of a frig but works.

Round() function giving sometimes incorrect results

Regards, Bill

Re: rounding down instead of up

tcullinane — Mon, 30 Mar 2015 14:16:29 GMT

no, floor will always round down, (or ceil will always round up) which appears to answer the title question, but not the problem.

I think something like Massimo has said, multiplying by 10,100 or whatever to give the required degree of accuracy then dividing to display with correct places of decimal is a good option.