Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Round to two decimal places

Highlighted
8 - Asteroid

Hi,

 

I have the below table and want to round Rent Balance to two decimal places and the result should match as in the Required column. I tried using the Round function but that does not give the desired result. Any ideas how I can use a formula or tool to achive this please.

 

 

Rent Balance

Required

356.4785714

356.48

356.4785714

356.48

356.4785714

356.48

329.0571429

329.06

301.6357143

301.64

274.2142857

274.21

246.7928571

246.79

219.3714286

219.37

191.95

191.95

164.5285714

164.53

521.0071429

521.01

521.0071429

521.01

493.5857143

493.59

466.1642857

466.16

 

 

Thanks

Jag

Highlighted
Alteryx
Alteryx

Hi @jagjit_singh, you can achieve this a couple of ways.

One, you can use a select tool and recast the type as a fixed decimal set to "19.2".

Two, you can use a formula tool and create a new field, referring to the original, again with a fixed decimal set to "19.2", where the 2 refers to the 2 decimal points.

See attached.

Highlighted
12 - Quasar
12 - Quasar

Rounding is one of those things that sounds simple but can be complex because people mean different things when they say "rounding".  Ken Black wrote a nice piece last year describing some of the different approaches and how to execute the rules in Alteryx:

https://3danim8.wordpress.com/2015/06/24/rounding-to-the-nearest-dollar-using-alteryx/

Highlighted
Alteryx
Alteryx

Great points, Jason. In my haste to answer the question (late at night), I missed the rounding part of the question, and focused on the 2 decimal points.

I believe my solution will get the answer, but the post you referred to is a great place to learn more about "all things rounding".

Highlighted
5 - Atom

Are you sure you you used the round function correctly? Its not like excel which you type number of digits, it ask for multiple. Therefore:

round([Rent Balance],.01)

Highlighted
5 - Atom

Hello!

 

FixedDecimal works well in a case like this. But what if you're bringing in a large amount of data with many columns that you want to cast as fixed decimal set to "8.3"?

 

In select, I can select multiple columns and change data type to FixedDecimal, but they will all be set to the default "19.2". Clicking the Size field and pasting or typing in "8.3" for every Column field gets tedious.

 

Is there a solution that would automate changing the data types of a large number of column fields to FixedDecimal set to "8.3"?

Highlighted
6 - Meteoroid

1. Transpose the desired columns

2. Make the decimal rounding change to the data field "value"

3. pivot the data back via Cross Tab

Highlighted
5 - Atom

@MikeN , Clear, precise and relevant. Thanks!

Highlighted
8 - Asteroid

Regarding the question: Is there a solution that would automate changing the data types of a large number of column fields to FixedDecimal set to "8.3"?

 

The answer is yes.  There is a multi-field formula tool.  When configuring this tool you will need to:

- Select the fields to be bulk updated

- Check box related to Change Output Type to 

- Select the data type FixedDecimal and provide the desired precision in the Size text box

- Enter the expression: [_CurrentField_] 

 

Highlighted
Alteryx Partner

Thanks for asking this question

Labels