Vælg en organisation
Custom Number Format Show Unexpected Number When the Value Is Between [-0.5 ~ 0]
Udgivelsesdato: Feb 28, 2025
Beskrivelse
When using a custom number format, the negative numbers are shown as positive numbers.
For example, when showing the numbers in Million, the negative profit show as [+0M] in Tableau.
Cause
Tableau rounds the numbers first and set the custom format.So the value between "-0.5~0" is converted to "0" and then the [+0M] is shown in the view.
Løsning
Option 1
Please create a String field to show the custom Number format.For example, the calculation could be as follows:
=============
IF SUM([Profit])/1000000>=0
THEN '+' + STR(ROUND(SUM([Profit])/1000000)) + 'M'
ELSEIF SUM([Profit])/1000000>-0.5
THEN '- 0M'
ELSE '- ' + STR(-ROUND(SUM([Profit])/1000000)) + 'M'
END
=============
Please check the attached workbook for details.
Option 2
Cast the rounded number as a string and concatenate it with a sign test, something like:`IF [original field] > 0 THEN "▲ " ELSEIF [original field] < 0 THEN "▼ " ELSE "" END + STR([rounded field])`
Option 3
Another method avoids string casting, which may be less performant: create a duplicate of the field and add it to the Text/Label shelf so there are two copies there, then break the custom formatting string in two so the first instance of the field displays the up arrow ▲ or down arrow ▼ according to the sign of the number, and the second instance just displays millions out to two decimal places. Since the copy with the arrows is not rounding, it does not lose the sign when the value rounds to zero.Lastly, ensure the two number fields display on the same line. Click the Text button on the Marks card, then the "..." button, and manually move the field references to the same line (just like editing a tooltip).
Please see the attached sample workbook for a demonstration.
Vidensartikelnummer
001474373
Løste denne artikel dit problem?
Giv os besked, så vi kan forbedre os!
