Handling Null and Other Special Values
Some data requires special handling in Tableau. Specifically:
- null values
- unrecognized or ambiguous geographic locations
- negative or zero values when working with a logarithmic scale
- negative or zero values when working with treemaps
Special values indicator
When your data contains any of these special values, Tableau cannot plot them in the view. Instead, it displays an indicator in the lower right corner of the view. Click the indicator to see options for how to handle these values.
If you don’t know how to handle the values, you can choose to leave the special values indicator. Generally, you should continue to show the indicator so that you know there is data that is not being shown in the view. However, to hide the indicator, right-click (control-click on a Mac) it and select Hide Indicator.
Null numbers and dates, and negative values on log axes
A null value is a field that is blank, and signifies missing or unknown values.
When you drag a measure or continuous date to the view, the values are shown along a continuous axis. If the field contains null values, or if there are zeroes or negative values on a logarithmic axis, Tableau cannot plot them. When these values exist, Tableau displays an indicator in the lower right corner of the view that says unknown values exist. Click the indicator and choose from the following options:
-
Filter Data - exclude the null values from the view using a filter. When you filter data, the null values are also excluded from any calculations used in the view.
-
Show Data at Default Position - show the data at a default location on the axis. The null values will still be included in calculations. The default position depends on the data type. The table below defines the defaults.
Type of Data Default Position Numbers 0 Dates 12/31/1899 Negative Values on a Log Axis 1 Unknown Geographic Location (0,0)
Hiding null values
- To filter null dimensions or discrete measures, drag the pill to the Filter shelf and deselect Null. The null value will appear in the list with discrete values, where you can then remove it.
- When a measure contains null values, they are usually plotted in a view as zero. To see other options, including how to suppress these null values altogether, see Format null values.
Note: See Logical Functions for functions, such as ISNULL() and IFNULL(), which you can use to handle null values with a calculated field.
Unknown geographic locations
When working with maps and geographic fields, unknown or ambiguous locations are identified by the indicator in the lower right corner of the view. Click the indicator and choose from the following options:
-
Edit Locations - correct the locations by mapping your data to known locations.
-
Filter Data - exclude the unknown locations from the view using a filter. The locations will not be included in calculations.
-
Show Data at Default Position - show the values at the default position of (0, 0) on the map.
Zero and negative values in treemaps
When working with treemaps, any null or zero values display in the indicator in the lower right corner of the view. Click the indicator and choose from the following options:
-
Filter Data - exclude the unknown locations from the view using a filter. The locations will not be included in calculations.
-
Use Absolute Values - use the absolute value to determine the size of the corresponding area in the view. For example, both values of 5 and -5 are shown as the same size.