KNOWLEDGE BASE

Filtering Out Null Values in YoY Growth Calculations


Product(s): Tableau Desktop, Tableau Public
Version(s): 8.1, 8.0, 7.0
Last Modified Date: 16 Aug 2016

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.

Example 1

In this case, you may want to hide the bar for the first year, because it shows zero instead of the difference from the previous year. Zero percent difference is probably not technically correct in the overall history of your business, so this can mislead viewers.

Example 2

In this case, the line representing zero percent difference in sales is even more distracting than in the previous example. And also as in the previous example, it does not convey your message properly.

Filter out the null values

The following table shows the percent of change in product sales over a range of years. Percent difference is one of the built-in quick table calculations, and it is applied to SUM(Sales). To hide the first year, which has no comparative data to display, you filter out the null values for the table calculation.

Step 1

On the Marks card, right-click SUM(Sales) and select Filter.

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

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.

 

Alternate Search Terms:Calculations Filters
Did this article resolve the issue?