Excluding Quick Table Calculation Values When No Data is Provided

Published: 19 May 2017
Last Modified Date: 20 Jul 2023

Question

How to display NULL when either value being compared in a percent difference quick table calculation is NULL.

For example, when adding a percent difference quick table calculation to a dimension and there is no data for the second half of the year, then the first month that has no data will show as -100%.

Environment

Tableau Desktop

Answer

Create a calculated field that uses table functions to produce the percent difference rather than a quick table calculation. The attached example workbook uses sample data to demonstrate the following directions:

Right-click and drag the date field (in this example: [Date]) onto the Rows shelf.
In the Drop Field dialog, select MY(Date) and click OK.
Drag the measure (in this example [Value]) to Text on the marks card.
Right-click SUM(Valuet) on the marks card and select Quick Table Calculation > Percent Difference
Open the calculation editor and drag SUM(Value) from the Marks card into the calculation editor.
- Tableau Desktop will automatically write out the formula it uses for percent differece, which is:
```
(ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / ABS(LOOKUP(ZN(SUM([Value])), -1))
```
- Remove the function ZN() from the formula. The function ZN() converts NULL values into zeros. The final calculation may look like:
```
(SUM([Value]) - LOOKUP(SUM([Value]), -1)) / ABS(LOOKUP(SUM([Value]), -1))
```
Name the calculation (in this example: "% Difference Amount") and then click OK.
Replace SUM(Value) on the Marks card with [% Difference Amount].
(Optional) Depending on how your new calculated field ranks within your view, you may need to edit the table calculation to Table(down)

Additional Information

By design quick table calculations treat NULL values, aka blank cells in the crosstab, as zeros.

To view the above steps in action, see the video below.
Note: the video has no sound.