This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 09-23-2016 02:00 PM - edited on 03-12-2020 11:02 AM by AYXAcademy
Whether for mathematical or formatting purposes, you may want to round your numeric data. Depending on the type of rounding you're looking to do, Alteryx has a solution for you!
To demonstrate rounding methods, we’ll use some sample data for distances that a truck traveled between two locations ([Total Distance]). As you can see, the data includes 11 values after the decimal point. That level of precision is probably unnecessary for our analyses. So, to better represent this data, let's 1) round it to the nearest mile, 2) round to the nearest 100 miles, 3) round to two decimal places and 4) round it up to the nearest mile.
1) Round to the nearest mile (integer):
The current data reflects the distance a truck traveled from one location to another. We want to round this data to the nearest mile. To do this, we’ll use the Round function (found under the Math category of functions in the Formula Tool). The Round function requires two parameters: the data you want to round, and the multiplier to which you want to round. In the case of rounding to the nearest integer, we will simply round to the nearest multiple of 1.
2) Round to Nearest 100 miles
If, say, we wanted to round to the nearest 100 miles, we’d simply adjust the multiplier to 100:
3) Round to Two Decimal Places:
If we needed to round to a certain number of decimal places (let’s say 2 decimal places*), we can change the multiplier to 0.01 :
*Note that the data type has been set to Double for accommodating decimal places in the rounded results.
4) Round-Up to the Nearest Mile (Integer):
You may find yourself with the need to round up (not necessarily to the nearest integer). In this case, we’ll need to turn to the Ceil function (also under the Math category of functions). The Ceil function will return the smallest integer greater than or equal to a value [x] or expression [Value]/[x]. So, to use an example from the sample data, Truck 1 traveled 567.712005 miles. To round up to the nearest mile, we’ll use the expression CEIL([Total Distance]/1) Because 567.712005 divided by 1 is itself, the smallest integer that is greater than or equal to 567.712005 is 568.
Interested in other discussions on rounding? You may want to check out these Community discussion threads on rounding up, rounding to a certain number of decimal places, and rounding to a multiple of (insert value here).
I found that I needed to also ensure that the numbers being rounded all have the same number of decimal places, which should be the max needed for any of them for rounding, before rounding, or it produces unexpected results.
For instance, if I have .8355, .0635, and .936 as 3 Double fields, using Round([_CurrentField_],.001) or changing the data types to FixedDecimal 19.3 both round them to .835, .064, and .936. You would think, since the 4th decimal place of .8355 and .0635 is 5, they should both round up, but they do not.
First, I needed to change the data type of all 3 to FixedDecimal 19.4 to get .8355, .0635, and .9360, then use the Round() function (not changing the FixedDecimal length), and finally I got .836, .064, and .936.
If I have to round multiple columns, do I need to create a formula for each column? or is there a more effective way to do so?
Thanks!
@Salicea4 you can use the Multi Field Formula tool.
Adding on to this thread instead of starting a new one. I have some interest rates (that are being read in as x.xx instead of 0.0xxx) and noticed that Alteryx is doing rounding a bit differently than expected.
For example:
1.255 rounds to 1.25 in Alteryx
4.255 rounds to 1.26 in Alteryx
Both of these round to x.26 in Excel.
I noticed that when I hand enter these numbers, Alteryx actually stores these as something like 1.254999999999999893418589635984972119331359863281250000000000000000000000000000000000000000
There is a definitive rounding formula to eliminiate the 999999999 issue?
@aaronindm a little background reading on the issue: Floating point numbers are surprisingly strange
That being said, I can't actually replicate the issue you're having. Perhaps you should post a new thread in Designer Discussions so that you can attach your workflow.
great examples thanks! @ChristineB you made it pretty much clear.