Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lakwinder
Contributor III

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
reddys310
Honored Contributor II

Re: Number formatting rounding up

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

17 Replies

Re: Number formatting rounding up

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

lakwinder
Contributor III

Re: Number formatting rounding up

Decimal or comma

def not full stop

lakwinder
Contributor III

Re: Number formatting rounding up

Sorry, decimal separator

Re: Number formatting rounding up

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

lakwinder
Contributor III

Re: Number formatting rounding up

445,566,900

needs to be

445,567

Re: Number formatting rounding up

May be this:

Round(445,566,900/1000)

lakwinder
Contributor III

Re: Number formatting rounding up

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?

Re: Number formatting rounding up

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

lakwinder
Contributor III

Re: Number formatting rounding up

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.

Community Browser