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: 
Anonymous
Not applicable

Number formatting rounding up

Hi Guys,

How do i format this number in a pivot table to round up.

Thanks

I want:

445,567

but i'm getting

445,566,900

1 Solution

Accepted Solutions
reddy-s
Master II
Master II

Hi Lak,

Try this.This should work 🙂

Ceil(sum(Sales),5) - so what this would do is round it only to multiples of 5

based on your example :

it would round 769.5 to 170

View solution in original post

18 Replies
sunny_talwar

Is fullstop (.) used as thousand separator or decimal separator here?

Anonymous
Not applicable
Author

Decimal or comma

def not full stop

Anonymous
Not applicable
Author

Sorry, decimal separator

sunny_talwar

I mean to ask is this 445,566.900 your number or 445.566.900?

Anonymous
Not applicable
Author

445,566,900

needs to be

445,567

sunny_talwar

May be this:

Round(445,566,900/1000)

Anonymous
Not applicable
Author

Ok that works, but there's a rounding issue when a number is bang on .5.

Excel correct figures...QS....No format...Floor....Round......Ceil

4                4.0                  4                    4                      4                        5

300            299.7            300                  299                  300                  300

770           769.5              769               769                    769                   770

It seems like i can't use any of them to get it spot on.  Maybe an if statement?

sunny_talwar

So when a number is 769.5, you want it to be 770???

Anonymous
Not applicable
Author

Yes, but numbers are in a table so i cant use Ceil as that is turning 4.0 into 5, but it still needs to be 5.