I saw there are functions CEIL() can change data from ceil(6.54) to 7 and Floor(6.54) to 6.
I would like to use roundup() functions.
Such as:
324 to 400
3425 to 4000
3425.123 to 4000
Anyone know how to do it? Thank you.
Karl,
i converted both Jason and Philip idea into more dynamic
i have created a text input to indetify the mapping for the conversion as below and Joined with length of source input the apply formula
the final output is as below
please test the attached workflow
The quickest way I know is to divided by the place you want to round to, use CEIL to round up, then multiply back by the place:
CEIL([Number] / 100) * 100
CEIL([Number] / 1000) * 100
Ceiling and floor just go to the nearest integer. The ROUND(x, multiple) function will allow you to specify the level of precision on which to round, very similar to Excel. But you can't specify the direction, so in your example if your did ROUND(3425.123,1000) it would return 3000.
The problem is sometimes we do not know the input data. So I can notsimply /100or /1000 or /10000.
If you would like a single formula which does this:
Ceil([Val]/Pow(10,Floor(Log10(Abs([Val]))))) * Pow(10,Floor(Log10(Abs([Val]))))
Attached as a workflow
The Pow(...) part works out the power of ten for the input number.
For the sake of self promotion, did a blog post on rounding Tableau and Alteryx a little while ago:
http://jdunkerley.co.uk/2015/07/29/rounding-calculations-in-tableau-and-excel-and-alteryx/
Great article James! I think they key with rounding is to first understand that different people/organizations all have different definitions on what they mean by rounding. You must understand specifically what you are trying to accomplish first, then select from one of these detailed methods to accomplish. If you simply round using 'out of the box' functions, it may be at odds with the accepted truth inside your organization.
Thank you, guys.
Your solutions and ideas are great helpful.
Have a need to Round Up to the nearest penny. This seems to work. The value I'm rounding is [Unit Price]
IF (([Unit Price] - FLOOR([Unit Price])) * 100) - FLOOR((([Unit Price] - FLOOR([Unit Price])) * 100)) = 0 THEN [Unit Price] ELSE FLOOR([Unit Price]) + (FLOOR((([Unit Price] - FLOOR([Unit Price])) * 100) ) / 100) + 0.01 ENDIF
$23.00 = $23.00
$23.10 = $23.10
$23.2000001 = $23.21
@KenMorrill try these two:
ceil([unit price]*100)/100
if regex_match(tostring([unit price]),".*\.\d{2}.*[987654321]+$") then (floor([unit price]*100)+1)/100 else [unit price] endif
sorry, wrong post
