ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to show 2 decimal places ONLY when not a whole number

stevecassidy7777
6 - Meteoroid

Hello-

 

I am looking for a way to format numbers so they have two decimal places but only if they are not a whole number. I can't seem to find a way. Any ideas?

 

CurrentDesired
  
1616
15.7511115.75
77
8.998.99
10.2222210.22
8.9918.99
16.116.10

 

 

Thanks,

Steve

 

 

 

NicoleJohnson
14 - Magnetar
14 - Magnetar

Use a rounding formula in a Formula tool to get to your two decimals: Round([Current],0.01) <-- This will round to the nearest hundredth.

 

Then make sure your field is a double data type, which will show the two decimals when not a whole number, but only the number itself if it is a whole number. 🙂

 

Cheers!

NJ

jdunkerley79
16 - Nebula
16 - Nebula

Assuming a string data type is an acceptable output:

 

REGEX_Replace(ToString([Current], 2), "\.00$", "")

 

The ToString will format with two decimal places. The Regex will trim 2 trailing 0s

 

stevecassidy7777
6 - Meteoroid

Thank you both. NJ, this is exactly what I was looking for!

 

 

Steve

Jax23
7 - Meteor

I tried this way and it doesnt work when I placed the formatted data into a table report, the .00 decimals re-appears.

Any other ways to achieve this?

I dont want to convert to string as I want my output as a number format.

Labels