조직 선택
Custom Number Format Show Unexpected Number When the Value Is Between [-0.5 ~ 0]
게시 일자: Feb 28, 2025
상세 설명
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.
솔루션
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.
Knowledge 기사 번호
001474373
이 기사를 통해 문제를 해결했습니까?
개선을 위한 의견을 보내주세요.
