5 Replies Latest reply on Sep 20, 2016 12:51 AM by david dansby

# Truncating numbers

Hi,

I have a column of numbers that I need truncated to 2 decimal places. Everything I have tried so far will get me to just 2 decimal places, but it is rounding.

For example 9.96666, I need this to be 9.96. Truncate is the right function, but it doesn't seem to exist in Tableau. Everything I have tried so far returns 9.97.

I have converted it to a string and then use a combination of "Find","Left" and "Mid" by using the location of the decimal place - this works, I end up with the right amount in every case, but it ends up as a string, and I need it to be a number.

Any suggestions on how to either just truncate the number, or convert my string concoction to a number?

Thanks,

Holly

• ###### 1. Re: Truncating numbers

Hey Holly,

You can convert your string back to a number with the FLOAT() function.

Ideally, though - if you could pass in a secondary parameter to FLOOR (like you can with ROUND), you would have been able to do this natively.

Hope this helps!

- Derrick

1 of 1 people found this helpful
• ###### 2. Re: Truncating numbers

Have you tried using the FLOAT() function on the calculated string field you created?

1 of 1 people found this helpful
• ###### 3. Re: Truncating numbers

A straight forward modification to the existing formula would be to convert the string back to a float with the FLOAT() function; wrap the truncation with the FLOAT function.

for example:

FLOAT(LEFT(STR(......))

1 of 1 people found this helpful
• ###### 4. Re: Truncating numbers

Thanks - FLOAT() worked. I tried INT(), but kept getting an error. It seemed not to like the string in some cases.

• ###### 5. Re: Truncating numbers

Holly,

I am trying truncate instead of round as well, but cannot figure out the how I can use LEFT RIGHT and STR to do so.  Can you please provide the full code you used to calculate the field you truncated please.  Thanks in advance for the help.