Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paul_ripley
Creator III
Creator III

Rounding using Floor/Ceil is there an alternative?

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

1 Solution

Accepted Solutions
atoz1158
Creator II
Creator II

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

View solution in original post

5 Replies
effinty2112
Master
Master

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

mangalsk
Creator III
Creator III

Hello ,

For total are you checking 'Sum of rows' then it will directly sum, else you can use 'expression total' it will calculte correctly2017-07-25_160437.png

paul_ripley
Creator III
Creator III
Author

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

atoz1158
Creator II
Creator II

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

mangalsk
Creator III
Creator III

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