KNOWLEDGE BASE

Returning Zero Instead of Excluding Values When No Records are in Filter Range


Published: 03 Feb 2014
Last Modified Date: 28 Apr 2016

Question

How to return zero instead of excluding a value completely when no records fall within the filter range.

Environment

Tableau Desktop

Answer

Use a calculated field and a parameter instead of a filter to control what values are returned.

Create the parameter:

  1. Right-click in the Parameters pane and select Create Parameter....
  2. Name the parameter Start Date and under Data Type choose Date.
  3. Under Allowable values select Range.
  4. Click Set From Field, select Order Date and click OK.
  5. Repeat steps 1-3 to create a parameter named End Date.

Create the calculated field:

  1. Right-click Sales and select Create Calculated Field....
  2. Name the calculated field Sales in Range.
  3. In Formula enter IF [Order Date] > [Start Date] AND [Order Date] < [End Date] THEN [Sales] ELSE 0 END.
  4. Click OK.

Build the view:

  1. Right-click Start Date and select Show Parameter Control.
  2. Right-click End Date and select Show Parameter Control.
  3. Drag Customer Name to the Rows shelf.
  4. Drag Sales in Range to Text in the Marks card.
  5. Adjust Start Date and End Date parameters appropriately.
Did this article resolve the issue?