KNOWLEDGE BASE

## Excluding Quick Table Calculation Values When No Data is Provided

Published: 19 May 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

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].