12 Replies Latest reply on Jul 26, 2018 2:57 PM by Joe Oppelt

# Properly format number

hi, i am trying to format my number as 5.9%, enclosed is the screenshot.... percent with 1 decimal = 6.0%, percent with 2 decimal = 5.99%.  i want 5.9%, why is it acting funny!

• ###### 1. Re: Properly format number

ROUND(SUM(1+CPD)*100,1)

• ###### 2. Re: Properly format number

If we take the example measure value and run it on the function ROUND(SUM(1+0.059868827) * 100,1) , the value becomes 106 with automatic formatting.

• ###### 3. Re: Properly format number

1+CPD is his Field Name

• ###### 4. Re: Properly format number

Ah, my bad! Tableau is still rounding it to 6.

• ###### 5. Re: Properly format number

Formatting rounds, not truncates.  6.0 is the correct one-decimal value for 5.99.

You can convert this to a string, find the decimal and truncate one character after the decimal, and then convert back to number.

Or you can use the FLOOR function.  FLOOR chops off a decimal down to the next whole number.  5.99 would FLOOR to 5.

So if you want to chop off to one decimal place, do:

This would take your 5.99 and make it 59.9, and FLOOR it to 59.0, and then divide that by 10 to make it 5.9.

• ###### 6. Re: Properly format number

Also, try the below in a calculated field.

FLOAT(LEFT(STR([1+CPD]*100),3))

Then on the formatting, set the following to get 5.9%

• ###### 7. Re: Properly format number

Joe Oppelt Heh glad someone caught that.

• ###### 8. Re: Properly format number

thanks, ill try in the morning...

but isnt setting the number (.05986476) format to percent and decimal to 0 it SHOULD equal  5.9%, correct?  why is Tableau rounding?  Tableau has very odd number formatting  (it seems to me), its bizarre i would need to run another calculation just to format a number to a proper percent

• ###### 9. Re: Properly format number

Just a point about using string stuff.  String manipulation is the least efficient among things Tableau does.  if we can keep dates as dates and numerics as numeric, it's best to do so.

• ###### 10. Re: Properly format number

Totally agree on to keep dates as dates and numerics as numeric! Since Tableau is rounding off, that's one of the way to present the content.

Just curious - how does string manipulation lead to more effort on tableau?

• ###### 11. Re: Properly format number

jon rios wrote:

...why is Tableau rounding? Tableau has very odd number formatting (it seems to me), its bizarre i would need to run another calculation just to format a number to a proper percent

That's standard.  It's not odd at all.

In my opinion what tableau should give us is a TRUNCATE numeric operation, but we don't have that.

Do the hack with FLOOR keeps everything numeric, and creates the desired output.

• ###### 12. Re: Properly format number

kunal.dongre.0 wrote:

Just curious - how does string manipulation lead to more effort on tableau?

It has to do with the engineering under the hood, which I don't have details for.

If you read through any knowledge-base paper about improving efficiency of your sheets, avoiding string manipulation when possible is one of the items commonly covered.

If you're manipulating a dozen rows, it doesn't matter.  If you are cycling through a million, it makes a difference.