1 2 Previous Next 19 Replies Latest reply on Jun 6, 2019 8:52 AM by Dan Rubin

# How do I round a decimal up to the next whole integer?

I have some very simple equations that result in a decimal product.  I need the result to always round up to the nearst integer.  I can not figure out how to force Tableau to take even the smallest fractions and round them to the next whole number.

Thank you

• ###### 1. Re: How do I round a decimal up to the next whole integer?

Here's a workbook with ceiling and floor calculations. There are some posts on it as well, but they are impossible to find...

1 of 1 people found this helpful
• ###### 2. Re: How do I round a decimal up to the next whole integer?

I've modified Alex's formula to make it general for flooring to any multiple. For example flooring 2.8 to nearest 0.5 is 2.5. Or flooring 32 to nearest 10 is 30.

So flooring function:

if [Value]/[Multiple]=int([Value]/[Multiple]) then [Value]

elseif [Value]<0 then int([Value]/[Multiple]-1)*[Multiple] else

int([Value]/[Multiple])*[Multiple]

end

Ceiling function:

if [Value]/[Multiple]=int([Value]/[Multiple]) then [Value] elseif [Value]<0 then int([Value]/[Multiple])*[Multiple] else

int([Value]/[Multiple]+1)*[Multiple]

end

Where [Muliple] is a float parameter

1 of 1 people found this helpful
• ###### 3. Re: How do I round a decimal up to the next whole integer?

Ok I just noticed that it doesn't work 100%. With multiple set to 0.1, it floors 2.4 to 2.3. It seems to think int(24) = 23. Any ideas why?

1 of 1 people found this helpful
• ###### 4. Re: How do I round a decimal up to the next whole integer?

I think your formula is unnecessarily complicated.

If using only positive values, it should be enough to say that

int ( expression ) + 1

since int() always rounds down (toward zero), adding 1 to whatever it does will be equivalent to rounding up.

Except in the case where expression is a whole integer from the start. In that case, what to do?

I'd say this (given X is the number/expression you want rounded)

if X = int(X) then X

else int(X) + 1

end

5 of 5 people found this helpful
• ###### 5. Re: How do I round a decimal up to the next whole integer?

General is:

if [Value]=int([Value]) then [Value] elseif [Value]<0 then int([Value]) else

int([Value]+1)

end

2 of 2 people found this helpful
• ###### 6. Re: How do I round a decimal up to the next whole integer?

Tableau has a a ROUND function, but not a ROUNDUP. To always round up X, use the following logic:

If is_integer (X) then X

else ROUND(X+0.5)

Tableau does not have an is_integer function. But it can probably be faked as:

is_integer(X) = X==ROUND(X)

where the '=' is the assignment operator, and the '==' is the equality testing operator

Hope this helps

1 of 1 people found this helpful
• ###### 7. Re: How do I round a decimal up to the next whole integer?

Let me get this straight…

1 year ago Emmitt asked a question;

1 year ago Alex Kerin replied;

11 months ago Mikhail added a modification;

11 months ago Mikhail corrected his modification;

6 months ago eraufus questioned Mikhail’s modification;

6 months ago Alex jumped back in with an answer to eraufus;

… and finally;

42 minutes ago Ratna added his understanding.

I love these forums!

--Shawn

(Really. The back and forth over time is incredibly interesting.)

1 of 1 people found this helpful
• ###### 8. Re: How do I round a decimal up to the next whole integer?

Hi Ratna,

FYI, the ROUND() function in Tableau (at least through v8.1) is a bit buggy in some cases and can return non-0 results when used with aggregates as an argument, it can be something like 5.000000000000000001 or 4.999999999999999999.

Here are floor and ceiling functions that just use INT() and avoid this. Floor and Ceiling Functions

Jonathan

1 of 1 people found this helpful
• ###### 9. Re: How do I round a decimal up to the next whole integer?

Hmmm...Emmit and all...

I was just rudely awakened to the fact that Tableau's round function doesn't consistently treat .5 as a round up or round down.  Most curious behavior.  (try placing the simple calculation round(.5,0) into a formula.  My expectation is that .5 would round up to 1, but instead it rounds down to zero.)

So, instead of using the .5 addition trick, I decided to create a formula for handling rounding:

iif(number%1>=.5, 1,0) + int(number)

if I wanted to round to the nearest 50, then the formula can be modified like this:

(iif(number%50/50 >= .5,1,0) + int(number/50)) * 50

This is a bit more complex for rounding up than the answer supplied by erafars and Alex Kerin, which handles rounding of negative and positive values:

if number = int(number) then number

elseif number < 0 then int(number) // handles rounding of negative

else int(number) + 1 // number isn't an integer or negative, so round up to next integer

end

www.kbearle.com/blog.aspx

2 of 2 people found this helpful
• ###### 10. Re: How do I round a decimal up to the next whole integer?

Surely this is a bug?

• ###### 11. Re: How do I round a decimal up to the next whole integer?

I'm late to this but had a similar need.

Wouldn't an easy solution just be.

IF [NUMBER]>INT([NUMBER]) THEN INT([NUMBER])+1 ELSE INT([NUMBER]) END

or does that cause some issue?

1 of 1 people found this helpful
• ###### 12. Re: How do I round a decimal up to the next whole integer?

Shawn Wallwork wrote:

Let me get this straight…

1 year ago Emmitt asked a question;

1 year ago Alex Kerin replied;

11 months ago Mikhail added a modification;

11 months ago Mikhail corrected his modification;

6 months ago eraufus questioned Mikhail’s modification;

6 months ago Alex jumped back in with an answer to eraufus;

… and finally;

42 minutes ago Ratna added his understanding.

I love these forums!

--Shawn

(Really. The back and forth over time is incredibly interesting.)

23 months later - Jeff joins the party

1 of 1 people found this helpful
• ###### 13. Re: How do I round a decimal up to the next whole integer?

And 14 minutes ago Mark posted a two year old comment I made with now out-of-date dates!

1 of 1 people found this helpful
• ###### 14. Re: How do I round a decimal up to the next whole integer?

And, it being so long a time period, Tableau now supports FLOOR() and

CEILING() functions that (for supported data sources) provide simple