topic Rounding problem in QlikView

Rounding problem

saimahasan — Tue, 28 Nov 2017 07:56:22 GMT

Hi,

I am stuck with rounding off. Basically i am trying to get the sum and then take the rounding value but wherever there is 5 after decimal it does not round whereas for rest of the values it rounds.

Please find attached sample data.

Re: Rounding problem

prieper — Tue, 28 Nov 2017 08:07:09 GMT

Do not see the problem, rounding works ....

ROUND(SUM(AMMORTISED_COST), 1) rounds the .5 up to the next full digit

FLOOR(SUM(AMMORTISED_COST), 1) would round all decimals down the the full digit ...

Re: Rounding problem

saimahasan — Tue, 28 Nov 2017 08:09:23 GMT

If i take =Round(Sum(MARKET_VALUE) - Sum(AMMORTISED_COST)) it doesnt work

Re: Rounding problem

saimahasan — Tue, 28 Nov 2017 08:14:57 GMT

Please look at this attachment.

Re: Rounding problem

prieper — Tue, 28 Nov 2017 08:27:13 GMT

Please use

Round(Sum(MARKET_VALUE) - Sum(AMMORTISED_COST), 1)

Re: Rounding problem

saimahasan — Tue, 28 Nov 2017 08:59:06 GMT

Not working

Re: Rounding problem

prieper — Tue, 28 Nov 2017 09:09:40 GMT

pfa

Re: Rounding problem

saimahasan — Tue, 28 Nov 2017 09:29:26 GMT

Thanks. Now i just want to clear my doubt. Is it that QV doesn't round off the number with decimal .5 to the nearest upper integer??

Re: Rounding problem

prieper — Tue, 28 Nov 2017 09:37:08 GMT

I cannot reproduce your behavior, as per help the default is set to "1":

Round(x[, step[, offset]])

Return data type: numeric

Arguments:

Argument	Description
x	Input number.
step	Interval increment. The default value is 1.
offset	Defines the base of the step interval. The default value is 0.

Even, if using ROUND without parameter, I get the result as desired.

Else please post an example of your qvw.

Re: Rounding problem

saimahasan — Tue, 28 Nov 2017 09:45:57 GMT

So, in your QVW the output which is getting generated is matching with mine. But can i get the output as -22531192 and not as -22531191 since the base number is -22531191.5

Re: Rounding problem

Anil_Babu_Samineni — Tue, 28 Nov 2017 09:49:19 GMT

Read this where we use floor() and ceil() in qlikview?

Re: Rounding problem

swuehl — Tue, 28 Nov 2017 10:03:33 GMT

Have a look at

Rounding Errors

You could load your values in as cents, like

LOAD Round(MARKET_VALUE*100) as MARKET_VALUE_CENT,

...

FROM ...;

Then do you sums, and only show the aggregated value divided by 100 to show your value in Dollar (or whatever your currency is)

=Round(Sum(MARKET_VALUE_CENT) /100, 0.01)