topic Re: Rounding using Floor/Ceil is there an alternative? in QlikView

Rounding using Floor/Ceil is there an alternative?

paul_ripley — Tue, 25 Jul 2017 10:27:08 GMT

Hello

In an expression I am rounding to the nearest million by /100000

Each individual row then appears correct to the nearest million but the total does not necessarily agree to the sum of the rounded individual lines.

I used the function floor, which works in the sense that the individual lines then always agree to the total but the logic of floor is wrong, ie £69,990,177 is rounded to £69m rather than £70m.

Is there anyway to have the individual lines rounded correctly ie to the nearest million but then also have the total agree to the individual lines?

Many thanks

Paul

Re: Rounding using Floor/Ceil is there an alternative?

effinty2112 — Tue, 25 Jul 2017 10:31:15 GMT

Hi Paul,

Your best bet is only to round individual lines. If you're working with a straight table then in the Expressions tab set the Total Mode to Sum of Rows.

Cheers

Andrew

Re: Rounding using Floor/Ceil is there an alternative?

mangalsk — Tue, 25 Jul 2017 10:35:32 GMT

Hello ,

For total are you checking 'Sum of rows' then it will directly sum, else you can use 'expression total' it will calculte correctly

Re: Rounding using Floor/Ceil is there an alternative?

paul_ripley — Tue, 25 Jul 2017 10:57:50 GMT

That didn't seem to work

You can see that the individual lines sum to 111, but the total says 112. I would need it really to show 111

expression

sum( {
<entity -= {"0148"},
pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}
> }
[GPR GBP])*-1/1000000

Re: Rounding using Floor/Ceil is there an alternative?

atoz1158 — Tue, 25 Jul 2017 11:04:34 GMT

Paul

I have used the following to round to the nearest ten (First line) I think what you would need is the second version

Floor(MyValue + 5, 10)

Floor(MyValue + 500000, 1000000)

Regards

Adrian

Re: Rounding using Floor/Ceil is there an alternative?

mangalsk — Tue, 25 Jul 2017 11:32:00 GMT

Please try once doing total as expression total, it should work