KNOWLEDGE BASE

Filtering Out Null Values in YoY Growth Calculations


Published: 27 Apr 2017
Last Modified Date: 27 Apr 2017

Question

How to filter out null values in year over year growth calculations. 

Environment

Tableau Desktop

Answer

Table calculations are computations that Tableau applies to the values in an entire table. How these calculations work is often dependent on the structure of the table. For example, in a sales environment, you can use a table calculation to compute the running total of sales across a specified date range, or to compare multiple months of sales and compute each month’s contribution to the total sales.

When you calculate comparisons such as year-over-year growth, the first year in your data table is empty because it has no previous years to compare itself to. If you want to hide that first year from the resulting Tableau view, a first thought might be to use a filter to remove it. However, doing that changes the calculation. Instead, you can use a filter to remove null (empty) values without changing the calculation. Here are a couple of examples for when you might want to hide the first year.

Examples can be found in the attached workbook Filtering Out Null Values.twbx. To filter out the null values, see the steps below:
  1. On the Marks card, right-click SUM(Sales) and select Filter.
  2. In the Filter dialog box, notice that, by default, Include null values is not selected, and then click OK.

Simply by clicking OK to instantiate the filter, you tell Tableau to remove the null values. Tableau filters table calculations after the data is retrieved from the database, whereas an Exclude filter is applied before the table calculation is performed.

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

 

Additional Information

Filtering can be useful in many situations. Two more examples are:

  • To show only the months that had negative growth.
  • To show only the months with greater than 10% growth.
Did this article resolve the issue?