Any time a calculation is done in Alteryx, it will keep all the detail provided. The downside is that this makes the number difficult to read and interpret quickly.

Rounding allows us to simplify a number while keeping it close enough to the original value that it doesn’t significantly impact the interpretation of the value. In Alteryx, we can round numbers in a few different ways depending on what outcome is desired.

The simplest way to round in Alteryx is the round function. This function takes two parameters, the value to round and the multiple you wish to round to. The multiple option defines how much detail is included in the rounded number.

Rounding numbers should be done as late as possible in a data pipeline because it can lead to a loss of precision and accuracy in the data. When numbers are rounded in Alteryx, their values are truncated, which can cause important details to be lost. Additionally, if rounding occurs earlier in the pipeline, subsequent calculations and analyses may be based on the rounded values rather than the original, more precise values. This can result in inaccurate conclusions and decisions being made. By delaying rounding until later in the pipeline, the data can be maintained in its most accurate form for as long as possible, minimizing the potential for errors and ensuring the integrity of the data.

The Round Function

The round function syntax is as follows:

round(x, mult)

The x parameter is the value you want to round, for example, 5.842. the mult parameter is the multiple you want to round to. This multiple works differently to what you would expect compared to MS Excel, Tableau or many other programming languages. In Alteryx the multiple is what you want to be able to evenly divide a number by, rather than how many decimal places you want to return.

So what does this mean? Basically, if you want to round to one decimal point, you would use the multiple of 0.1. Compare this excel where you would round to a number of 1. Why does Alteryx have this different system? It provides much more flexibility in how you want to round. Do you want to round to the nearest £1000? Then use a multiple of 1000. Want to round to the nearest 5 hundredths? Then use 0.05.

Other Rounding Formulas

In some situations, the rounding you want is not the rounding to a significant figure or decimal. Instead, you might want to round up or down specifically to the nearest integer value. Alteryx has two functions, Ceil and Floor, which enable this capability.

The Ceil Function

The Ceil function takes one parameter, the value x you wish to round, and will find the nearest whole number greater than or equal to x. This means you can take decimal values, like the average number of people that use a meeting room, and ensure the rounded value will always be included; a partially rounded person count is included as a whole.

An application of this is the number 5.842 would be rounded to 6 with the ceil function, so would 5.12 and 5.532 but the number 6.00001 would be rounded to 7. If you are rounding a negative number, like -5.234, it still rounds up in the positive direction, so CEIL(-5.234) becomes -5.

Summarising:

CEIL(5.842) becomes 6

CEIL(5.12) becomes 6

CEIL(6.0001) becomes 7

CEIL(-5.234) becomes -5

The Floor Function

The Floor function takes a similar logic as the ceil function, but instead of rounding up to the nearest whole number will round down. So, the floor function will round down to the earnest whole integer.

If you run the floor on the same values as above, the results would be:

FLOOR(5.842) becomes 5

FLOOR (5.12) becomes 5

FLOOR (6.0001) becomes 6

FLOOR (-5.234) becomes -6

Rounding in Reports

When creating reports, you can round directly in the reporting tools. Frustratingly, rounding in the reporting tools behaves differently to the round formula, but this behaviour is more familiar to excel rounding.

The tools that allow you to round are the Report Text and the Table tools. These two tools provide an additional option on how the numbers are displayed when populated into the report. This rounding is done row-by-row in the same manner that a report output is generated normally.

In the text tool, when you are adding a double or float field into your text output. In this tool, the field is then identified with square brackets surrounding the field name followed by a number indicating how many decimal points are included in the resulting output. This would appear as:

[numbers:2]

Where “numbers” is the field name and 2 is the number of decimal points in the output.

When you apply rounding using the Table tool, a similar process is applied by selecting a column with a double or float and then setting the “Dec. Places” configuration.  

In both the report rounding examples, the rounded output is purely based on the number of figures after the decimal point. This is the same method in Excel, Tableau and many other programming languages.

The Fixed Decimal Data Type

The final method you can use to round values is the Fixed Decimal data type. Using the fixed data type encodes the rounding into the field. The format for the field size is based on Precision and Scale. The precision is the maximum number of figures that can fit into the field, while the scale is how many figures are after the decimal point. You also need to include in the precision the decimal point as one of the figures.

Now how does this apply? With a Precision of 4 and Scale of 2, the number 4.309437 would be rounded to 4.31, and the number 6.532927 would be rounded to 6.53. But 74.927715 would be outside the field size and return a null value. This is because, with 4-precision and 2-scale, the maximum value that can fit in the field is 9.99.

Something else to consider is the negative sign will take one value of the precision. This means that a value of -74.927715 with a 6-precision and 2-scale would return a value of -74.93, but if you use a 5-precision and 2-scale, a conversion error is generated and a null value.

Conclusion

Alteryx provides several methods to round numbers, depending on the desired outcome. The Round function is the simplest (and most common) method that rounds a number to a multiple of the desired precision. The Ceil and Floor functions are used to round numbers up or down to the nearest integer value, respectively. When creating reports, rounding can be done directly in the reporting tools, which behave differently from the Round function. The Fixed Decimal data type allows for encoding rounding into the field, but it only takes positive values. By knowing these methods, users can choose the most appropriate way to round numbers for their analysis and reporting needs.