KNOWLEDGE BASE

Add a Calculated Field to the Filter Shelf


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

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


Using filters is one way of refining the data shown in your view. However, in some cases Tableau might not allow you to use a calculated field as a filter; for example, when the calculated field is a discrete aggregated field. Tableau does not support placing on the Filters shelf a calculated field that contains an aggregation that results in discrete values; such as sum, average, or attribute.

The following shows an example of such a calculation that cannot be used as a filter:

IF AVG([Sales])>2000 THEN 'Show' ELSE 'Hide' END

To work around this, you can do any of the following tasks:

Note: To handle aggregated calculations on the Filter shelf, Tableau uses a variation of Multipass SQL. However, because many databases allow only numeric or simple operations, Tableau does not support aggregated calculations that contain both numbers and strings.

To prepare the Tableau workbook for any of the tasks in the list above, complete the following steps.

Step 1

Open a new workbook and connect to the Superstore sample.

Step 2

Drag Sales to the Columns shelf and Customer Name to the Rows shelf.

Step 3

On the Marks card drop-down list of views, select Text.

Step 4

On the Marks card, right-click SUM(Sales) and select Measure > Average.

Step 5

Select Analysis > Create Calculated Field.

Step 6

In the Calculated Field dialog box, complete the following steps.

  1. For Name, type Avg Sales Over 2000.
  2. In the Formula box, build the following formula:

    IF AVG([Sales])>2000 THEN 'Show' ELSE 'Hide' END

  3. Confirm that the status message indicates that the formula is valid, and then click OK.

You can now complete any of the three options to effectively filter the view based on the calculated field.

Hide the values that are not part of the results

Step 1

From the Measures pane, drag the Avg Sales Over 2000 field to the Rows shelf.

The Show and Hide values appear.

Step 2

Right-click an instance of Hide and select Hide.

The rows that included the word Hide become hidden from the view.

Use a continuous aggregate

You can edit the calculation so that it is no longer a discrete dimension. You do this by changing the words Show and Hide to the integers 1 and 0.

Step 1

Select Analysis > Edit Calculated Field > Avg Sales Over 2000, and then change the calculation to the following:

If AVG([Sales])>2000 THEN 1 ELSE 0 END

In this example, 1 represents Show and 0 represents Hide.

Step 2

In the Measures pane, right-click the modified calculation, and then select Convert to Continuous.

Step 3

Drag the modified calculation onto the Filters shelf.

The Filter dialog box appears, showing the range of values from 0 to 1.

Step 4

For Range of values, set a range from to 1 to 1, and click OK.

The values below $2000 are filtered from the view.

Convert the values into a table calculation

Step 1

Create a second calculation, which looks like the following example:

LOOKUP([Avg Sales Over 2000], 0)

Step 2

Drag this calculation onto the Filters shelf.

Step 3

In the Filters dialog box, filter the range of values as in the previous section, so that they show from 1 to 1, and then click OK.

The view shows only the values over $2000.

 

Alternate Search Terms:cannot add calculated field, can't add calculation, filter shelf,
Did this article resolve the issue?