Becoming a Tableau Desktop Specialist – Part 6: Sets, Groups and Filters
Oct 16, 2019Christine Rietmann
Welcome back! This week I will write about Sets, Groups and Filters. These are all useful instruments for analyzing your data.
Check out the further posts of this series to learn more about it:
Creating a set allows you to define a subset of data as a new custom field. Once you created your set it appears at the bottom of your data pane. The icon typically shows two overlapping circles, but some other icons are possible.
The following overview can be found in the free training video of Tableau.
There are two options to create a set and we have to distinguish between dynamic sets and constant sets.
It is important to notice that these sets are not the same. When you look at the editing interface you can see the difference, but let’s start from the beginning.
The first option I am going to show you will only allow to create constant sets. Therefore, you select marks from the view and click on the icon in the tooltip.
This will create a set with all marks being selected by default, but it is also possible to exclude these marks which means that all the other marks will be a set. If you chose some marks by mistake, you could remove them by clicking the ‘red x’.
If you create a new set in the same view you can choose between:
The second option is for creating constant and dynamic sets. Click on the dimension and say ‘Create’ and then choose ‘Set’. Now the editing face will open. Your options are similar to filter options. If you want to create a dynamic set you have to choose ‘Condition’ or ‘Top’. Every time you update your underlying data the set will automatically be updated, too.
The additional option to combine sets generates further value for your analysis. To combine sets click on the set at the data pane and say ‘Create Combined Set’.
Another powerful way of defining set members is to use dashboard set actions.
Groups slice our dimension and can help us to better organize our data. For instance, we could group our products into certain sub-categories and create a hierarchy.
Groups let us put data together in a way that makes sense for our analysis. Maybe we are not interested in sub-categories but we want to know which products make the most profit and which make less profit. So, we can also group our products by ‘High-profit’, ‘Low-profit’ and ‘Others’.
A group is limited dynamic. This means that if the data set, for instance, gets updated and a new product is given, the new product will automatically put into the group ‘Others’. You can’t define a condition. This causes the problem that even if your ‘new’ product generates high-profit’ it will be sorted to ‘Others’.
Creating a group is very similar to creating a set. We have again the option to create a group from the view or from the data pane.
When we create a group from the view we distinguish between:
I had no idea what the differences between these two options could be. So I asked Klaus for help. He had also never heard about this but it didn’t take him long to figure out what the difference is.
To create a visual group you only have to select the members/marks you want to include. Similar to creating a set, you can then use the icon in the tooltip to create the group.
What happens is that your group will be added to the color shelf and highlighted in the view. You can also find it in the data pane with the typical symbol for a group: the paperclip.
Instead of selecting marks to create the group, you can also select headers (or labels, under the assumption you have a discrete dimension in your view).
This time your view will change. The selected items will be replaced by the group. Also, there won’t be highlighted items like we saw with the visual grouping.
Nevertheless, you can find the new group in the data pane.
To create a group from the data pane you have to right-click on a dimension and then chose ‘Create’ and say ‘Group’. A window will open where you can define your groups by marking the labels and then clicking on the group-button.
Tableau often organizes, for instance, date fields in one hierarchy, which we can use to drill down in our view. And of course, you can create own hierarchies. The only thing you have to do is to drag and drop your field on another field. Then the following window will open and you can set a name for your hierarchy.
Another option is – how could it be otherwise – right-click on the field and then say ‘Hierarchy’. Now you can choose between adding your field to an existing hierarchy or creating a new hierarchy. The same steps are necessary to remove a field from a hierarchy.
I think every one of us has an idea of what a filter does. Filters limit the number of records that are included in our view. (You can check that if you look at the number of marks, which Tableau gives you at the bottom left corner.)
It is very useful to have a further look at filters because in Tableau we can distinguish between 6 types of filters.
For the Desktop Specialist Exam, it is enough to concentrate on context filters, dimension filters, and measure filters.
The most common way to filter is to add a pill from the data pane to the filter shelf. Then the filter dialog box will open and you can define your filter. Filter options will vary depending on the field you drag to the filter shelf.
As we talked about dimensions we’ve learned that in most cases dimensions contain discrete categorical data. If this is given, we can include or exclude values in the view.
On the Tableau website, you can find this explanation of the four tabs which are given in the dialog box.
General: Use the General tab to select the values you want to include or exclude.
Wildcard: Use the Wildcard tab to define a pattern to filter on. For example, when filtering on email addresses you might want to only include emails from a specific domain. You can define a wildcard filter that ends with “@gmail.com” to only include Google email addresses.
Condition: Use the Condition tab in the Filter dialog box to define rules to filter by. For example, in a view showing the average Unit Price for a collection of products, you may want to only show the Products that have an average unit price that is greater than or equal to $25. You can use the built-in controls to write a condition or you can write a custom formula.
Top: Use the Top tab in the Filter dialog box to define a formula that computes the data that will be included in the view. For example, in a view that shows the average Time to Ship for a collection of products, you can decide to only show the top 15 products by Sales. Rather than having to define a specific range for Sales (e.g., greater than $100,000), you can define a limit (top 15) that is relative to the other members in the field (products).Source: Tableau Website
Remember what we said about measures. They normally contain quantitative data. So, we have to choose which range we want to filter. Due to the aggregation Tableau builds by default we first have to decide how our field should be aggregated.
I insert again the explanation from the Tableau website:
Range of Values: Select the Range of Values option to specify the minimum and maximum values of the range to include in the view. The values you specify are included in the range.
At Least: Select the At Least option to include all values that are greater than or equal to a specified minimum value. This type of filter is useful when the data changes often so specifying an upper limit may not be possible
Most: Select the At Most option to include all values that are less than or equal to a specified maximum value. This type of filter is useful when the data changes often so specifying a lower limit may not be possible.
Special (Tableau Desktop only): Select the Special option to filter on Null values. Include only Null values, Non-null values, or All Values.Source: Tableau Website
You have probably already realized that all topics are connected. Dates for instance were our common example for data fields which can either be continuous or discrete. This is why you won’t be surprised that you can filter a range of dates or discrete dates.
Depending on your selection your data field on the filter shelf will be green or blue.
How du you want to filter on?
Filter relative dates: Click Relative dates to define a range of dates that updates based on the date and time you open the view. For example, you may want to see Year to Date sales, all records from the past 30 days or bugs closed last week. Relative date filters can also be relative to a specific anchor date rather than today.
Filter a range of dates: Select Range of dates to define a fixed range of dates to filter. For example, you may want to see all orders placed between March 1, 2019, and June 12, 2019.
Filter discrete dates: Select a discrete date value in the dialog box if you want to include entire date levels. For example, if you select Quarters, you can choose to filter specific quarters (e.g. Q1, Q2, Q3, Q4) from your view, regardless of the year.
Latest date preset: If you want to ensure that only the most recent date in a data source is selected in the filter when the workbook is shared or opened, select a discrete date such as Month/Day/Year or Individual Dates and then, on the General tab, select Filter to latest date value when workbook is opened.
Filter individual dates: Select Individual dates to filter specific dates from your view.
Additional date filter options: When you select Relative dates or Range of dates, the Filter dialog box opens. In that dialog box, you can define a Starting date or Ending date. You can also select Special to include null dates, non-null dates, or all dates.Source: Tableau Website
After choosing your way of filtering the next window will look similar to one of the windows we discussed earlier.
Dimension filters can be converted to context filters. What happens is that all your remaining dimension and measure filters are no longer independent, because they will only filter the data which passes through the context filter.
This is due to the order of operations. As you can see, context filters are applied before dimension or measure filters.
The image also shows that some calculations are computed before the data is filtered. Every time you use a fixed LOD in your view which needs to be filtered, you have to add the filter to context filter. Otherwise, the fixed LOD will be computed before the data gets filtered.
I’ve struggled so many times with LODs and filters, but not anymore 🙂
I am happy about your feedback, you can reach out to me on Twitter or in the comments below.