Give a chestnut! Tableau Tips (103): Only filter the view without filtering the data

published: 2021-06-05

When we use filters in Tableau, the dimensional filter will filter out the original data as well as the data in the view. But sometimes, we don't want the data in the view to change, especially in the scenario of calculating the total percentage, calculating the overall ranking, moving average or running total.

For example: in the analysis view below, we sort the product subcategories by sales. Pay attention to the ranking of "appliances" in the category of office supplies. Among all products, its sales rank second (as shown in the figure below).

Next, we want to look at the specific rankings of the subcategories under office supplies, and use the filter on the right to select only "office supplies". At this point, you will find that the ranking of ‘Appliance’ has changed from the second place to the first place (as shown in the figure below). This is obviously not what we want!

 

According to business needs, we hope that even if different categories are filtered, the sorting results of subcategories will still maintain its original sorting (as shown in the figure below), instead of changing the established sorting due to filtering.

 

So, how to achieve it: only filter the view without filtering the data? Here to share the method for everyone.

In this issue of "Give a Chestnut", the Tableau trick that Ada wants to share with you is: only filter the view without filtering the data.

To facilitate learning, Lizi uses Tableau's own supermarket data source.

Tips: This involves a knowledge point of table calculation-the filter based on table calculation will not filter the original data. Will hide data in the view, and allow dimension members to be hidden in the view, without affecting the data in the view.

Specific steps are as follows:

1. The first step

Drag the subcategory to the row, drag the sales to the column, and then sort in descending order.

2. The second step

Create a calculated field: index(), this function indexes the rows, name it "sort", and drag the field to the row, as shown in the figure.

3. The third step

Create another calculated field: lookup(max(category),0), this function will return the value of the expression in the target row, 0 is specified as the relative offset from the current row as 0, drag the field to the filter, Show the filter, and filter.

In this way, there is only one ranking for each sub-category, and the ranking will not be regained because of the selection of the category. Filters based on table calculations will not filter out the underlying data, but will hide the data in the view and allow dimension members to be hidden in the view without affecting the data in the view.

Have you gotten the Tableau skills in this issue? Give it a try!