KNOWLEDGE BASE

Excluding Quick Table Calculation Values When No Data is Provided


Published: 19 May 2017
Last Modified Date: 17 Aug 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.
    1. Tableau Desktop will automatically write out the formula it uses for percent different, which is: 
      (ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / ABS(LOOKUP(ZN(SUM([Value])), -1))
    2. Remove the function ZN() from the formula. The function ZN() converts NULL values into zeros. The final calculation may look like: 
      (ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / ABS(LOOKUP(ZN(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?