KNOWLEDGE BASE

Excluding Quick Table Calculation Values When No Data is Provided


Published: 19 May 2017
Last Modified Date: 11 Dec 2017

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:
  1. Right-click and drag the date field (in this example: [Date]) onto the Rows shelf.
  2. In the Drop Field dialog, select MY(Date) and click OK.
  3. Drag the measure (in this example [Value]) to Text on the marks card.
  4. Right-click SUM(Valuet) on the marks card and select Quick Table Calculation > Percent Difference From
  5. 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))
      
  6. Name the calculation (in this example: "% Difference Amount") and then click OK.
  7. Replace SUM(Value) on the Marks card with [% Difference Amount].

Additional Information

By design quick table calculations treat NULL values, aka blank cells in the crosstab, as zeros.
Did this article resolve the issue?