
1. Re: How do I round a decimal up to the next whole integer?
Alex Kerin Oct 2, 2012 3:05 PM (in response to Emmitt Nantz)Here's a workbook with ceiling and floor calculations. There are some posts on it as well, but they are impossible to find...
EDIT: Here's one thread

Floor and Ceiling.twbx.zip 9.3 KB


2. Re: How do I round a decimal up to the next whole integer?
Mikhail Venkov Feb 19, 2013 11:01 AM (in response to Alex Kerin)1 of 1 people found this helpfulI'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

3. Re: How do I round a decimal up to the next whole integer?
Mikhail Venkov Feb 19, 2013 11:05 AM (in response to Mikhail Venkov)1 of 1 people found this helpfulOk 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?

4. Re: How do I round a decimal up to the next whole integer?
eraufars Jul 17, 2013 2:33 AM (in response to Mikhail Venkov)3 of 3 people found this helpfulI 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. Re: How do I round a decimal up to the next whole integer?
Alex Kerin Jul 17, 2013 7:41 AM (in response to eraufars)1 of 1 people found this helpfulGeneral is:
if [Value]=int([Value]) then [Value] elseif [Value]<0 then int([Value]) else
int([Value]+1)
end

6. Re: How do I round a decimal up to the next whole integer?
Ratna Sarkar Jan 6, 2014 3:26 PM (in response to Emmitt Nantz)1 of 1 people found this helpfulTableau 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

7. Re: How do I round a decimal up to the next whole integer?
Shawn Wallwork Jan 6, 2014 4:27 PM (in response to Ratna Sarkar)1 of 1 people found this helpfulLet 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.)

8. Re: How do I round a decimal up to the next whole integer?
Jonathan Drummey Jan 7, 2014 3:43 AM (in response to Ratna Sarkar)1 of 1 people found this helpfulHi Ratna,
FYI, the ROUND() function in Tableau (at least through v8.1) is a bit buggy in some cases and can return non0 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

9. Re: How do I round a decimal up to the next whole integer?
brad.earle.1 Feb 6, 2014 9:48 PM (in response to Emmitt Nantz)2 of 2 people found this helpfulHmmm...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
Brad Earle
www.kbearle.com/blog.aspx

10. Re: How do I round a decimal up to the next whole integer?
Alex Kerin Feb 7, 2014 9:16 AM (in response to brad.earle.1)Surely this is a bug?

11. Re: How do I round a decimal up to the next whole integer?
Jeffrey Smith Nov 6, 2015 6:57 AM (in response to Emmitt Nantz)1 of 1 people found this helpfulI'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?

12. Re: How do I round a decimal up to the next whole integer?
Mark Fraser Nov 6, 2015 7:07 AM (in response to Shawn Wallwork)1 of 1 people found this helpfulShawn 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

13. Re: How do I round a decimal up to the next whole integer?
Shawn Wallwork Nov 6, 2015 7:25 AM (in response to Mark Fraser)1 of 1 people found this helpfulAnd 14 minutes ago Mark posted a two year old comment I made with now outofdate dates!

14. Re: How do I round a decimal up to the next whole integer?
Jonathan Drummey Nov 6, 2015 7:59 AM (in response to Mark Fraser)1 of 1 people found this helpfulAnd, it being so long a time period, Tableau now supports FLOOR() and
CEILING() functions that (for supported data sources) provide simple
answers to the original question.
On Fri, Nov 6, 2015 at 10:08 AM, Mark Fraser <