Solved: Re: Number formatting rounding up - Qlik Community

Anonymous · ‎2016-01-04

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

reddy-s · ‎2016-01-05

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

sunny_talwar · ‎2016-01-04

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

Anonymous · ‎2016-01-04

Decimal or comma

def not full stop

Anonymous · ‎2016-01-04

Sorry, decimal separator

sunny_talwar · ‎2016-01-04

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

Anonymous · ‎2016-01-04

445,566,900

needs to be

445,567

sunny_talwar · ‎2016-01-04

May be this:

Round(445,566,900/1000)

Anonymous · ‎2016-01-04

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 · ‎2016-01-04

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

Anonymous · ‎2016-01-04

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.