Sorting isn’t the only way to arrange data. Creating drill-down hierarchies are easy in Tableau. Perhaps your data include a dimension set with too many members for convenient viewing. Grouping dimensions within a particular field is also available. Interacting with your data may uncover measurement outliers that you would like to save and reuse in other visualizations. That capability is enabled via sets. Even a group of sets can be created on-the-fly.
Hierarchies provide a way to start with a high-level overview of your data and then drill down to lower levels of detail on demand. In the below diagram, you can see a two-level view of the data that includes product category and then sub-category. That presentation may include more detail than you prefer to see. A hierarchy that combines category and sub-category can address both needs. Below diagram uses a hierarchy to show category first and sub-category on demand.
Hierarchy using category and sub-category Diagram :
Making a custom hierarchy Diagram :
The bar chart on the left displays the summary product category. By pointing at the category heading, a small plus sign will appear. Clicking on that causes the sub-category level of detail to be exposed. To collapse the hierarchy, the point at the category heading again and click on the minus sign. You can create as many levels in your hierarchy as you desire.
Hierarchies are created by pointing at a dimension field and dragging it on top of another field. The order of appearance is defined by dragging the field names contained within the hierarchy icon to the desired position. Below diagram shows the hierarchy icon with category and sub-category. You can change the hierarchy name by pointing at the text to the right of the hierarchy icon and typing product hierarchy.
Other fields can be added to the hierarchy by positioning them in the order desired inside the hierarchy grouping, on the dimension shelf.
[Related Article - What Are The Common Use Cases For Tabcmd In Tableau?]
There are a few different ways to add filtering to your visualization. Dragging any dimension or measure onto the filter shelf provides filtering that is accessible to the designer. Make that filter accessible to more people by turning it into a Quick Filter. This places it on the desktop where it is accessible to anyone— even those reading your report via Tableau Reader or Tableau Server. You can also create conditional filters that operate according to the rules you define.
In the below diagram, the category and sub-category view contain seventeen different rows of data. Suppose you want to hide five of those rows from view. Dragging the sub-category field from the dimension shelf and placing it in the filter shelf exposes the filter menu. Below diagram shows the filtered data with the general tab of the filter menu. The sub-categories that do not have checkmarks have been filtered out of view.
Diagram of Applying a filter via the filter shelf :
Notice that there are three other tabs on the filter menu. The Wildcard tab is typically used to search for text strings to filter. If you want to filter using another field that isn’t in your view you can use the Condition tab to select any field in your data source and filter using that field. The Top tab facilitates building the top and bottom filtering or filtering requiring other formula conditions.
If you use more than one of the filtering option tabs to define your filter, Tableau applies the conditions defined in each tab in the order tabs appearing from left to right. General conditions will be applied first, then wildcard, then condition, and the top tab conditions last.
Below the general field list to the right of the None button, there is a checkbox for the Exclude option. If Exclude is checked, the items that include checkmarks are filtered out of view. Exclude filters can take a little longer to execute than Include filters, especially if your data set is very large.
[Related Article - Tableau Competitors]
If you want to make the filter available for people that are viewing the report via Tableau Reader or Server you need to expose the filter control on the desktop. To create a Quick Filter, point and right-click on any pill used on any shelf in your worksheet, then select the Show Quick Filter option. Below diagram includes Quick Filters using the category and sales fields.
Diagram of Adding quick filters to the desktop :
The default quick filter styles are dependent on the type of field you apply within the quick filter control. In below diagram, the discrete category field results in discrete filter options (furniture office supplies, technology). Discrete filters are expressed using radio buttons or multi-select boxes. The second quick filter for sales (a continuous range of values) is expressed using slider-type filters.
Editing the quick filter type can be done from inside the quick filter itself. Click on the title bar of the filter to expose the available options. Below diagram shows examples of the menus that can be activated from the category and sales quick filter title bars.
Editing quick filter types Diagram :
The menu on the left side of the below diagram relates to discrete category filters. The right menu is for continuous filters. In addition to controlling the filter style, you can adjust many other attributes. You can edit the titles of each filter by including the words, discrete and continuous and applying a different colour to each word and centring the title. The quick filter titles in below diagram have been modified in this way.
These are the quick filter menus (both continuous and discrete):
These are the Quick Filter menu items that appear only if the Quick Filter is on a dashboard:
The remaining sections of each filter type control the style of quick filter. There are seven styles of discrete and three styles of continuous quick filter types available. An additional feature available directly from the quick filter is the ability to control the relevant values displayed directly from the desktop. Below diagram displays a small control (three bars).
Diagram of including all or relevant values :
This is important when you have several quick filters exposed in a view. For example, a hierarchy of quick filters might include a filter to select a state, then the city. Restricting the city filter to include only the relevant values means that if a particular state (Georgia) is selected in the first quick filter, the city quick filter would only display cities in the state of Georgia. If the city filter didn’t apply only relevant values, the filter would contain every city in the United States.
One type of filter that many experienced Tableau users are unaware of is the context filter. Context filters do not only filter the data, but they also cause Tableau to create a temporary table that contains only the filtered data. For this reason, they execute more slowly than a normal filter. Context filters are denoted by a grey-coloured pill. They can be useful if you want to work with a subset to achieve a particular result. Don’t use a context filter if you plan to alter the filter frequently.
Tableau provides robust filtering. In Chapter 8 on dashboards, you’ll learn how to save space on dashboards by making the data act as a filter by using actions to apply filters.
When you have a dimension that contains many members and your source data doesn’t include a hierarchy structure, grouping can provide a summarized view of the data. You can manually group items from headers or multi-select marks in a chart. Tableau also provides a menu option with a fuzzy search that will help your group by searching strings in large lists of values. You can even group by selecting marks in a view. If you need to work with data that isn’t structured the way you want it, grouping allows you to build that structure within Tableau.
[Related Article - Tableau Case Statements]
Below diagram includes a bar chart that compares product sub-categories within each product category. The office supplies dimension has too many small members with very low sales values. Grouping the six smallest categories in office supplies into a single (ad hoc) category creates a grouping that is more comparable to the other sub-categories.
There are three ways to group headings. The easiest way is to click on the paper clip icon in the Tooltips that appears when you multi-select the headers. The second way is to right-click after selecting the headings and pick the Group option on the menu. One final option is available via the paper clip icon in the toolbar.
After creating the group, all six members will be combined into a single bar. The name that appears in the heading will be a concatenated list of the individual headings. To rename the combined list heading, right-click while pointing at the new group, choose edit alias, and type in a shorter name. The example group will be called (Other offices). Below diagram shows the new group and group name. Now each category includes four members. Eliminating the tiny bars seen in below diagram is difficult to see and compare.
You can also create groups by selecting marks in the worksheet. This method is a great way to highlight items of interest when you are performing ad hoc analysis. In the below diagram, you see a cluster of marks that have been selected.
These marks can be grouped using the paper-clip icon in the Tooltip menu that appears when you point at any of the selected marks.
You can select All Dimensions to create the group. The result is shown in the below diagram.
Diagram of Grouping from headers :
Diagram of the ad hoc office group :
Tableau’s visual grouping causes the selected marks to be highlighted using a different colour than the marks that are not included in the group. These methods work well if you have a small number of members of a group or you can easily select the marks that you want to highlight.
Diagram of grouping marks using all dimensions :
Diagram of manually selecting a group :
If you have a very large set of dimensions that you need to group or the grouping must be created using portions of field names-these methods would be tedious. Tableau provides a more robust way to create groups using fuzzy search. Below diagram shows another grouping menu that can be accessed by right-clicking on a specific dimension field within the dimension shelf.
Diagram using a string search to the group :
You can also group products by the vendor. Below diagram shows a search for all products provided by the vendor Bevis. Using the find member search, tableau executes a string search in all the product names that include that string after checking to ensure that the group contains the correct information, clicking the Group button will create a new grouping of the products. You can also alias the group name within the menu. After completing all the vendor groups you require, selecting the Include Other checkbox will generate a group that contains all the other items in the dimension that haven’t already been assigned to a vendor group.
Please note that any new group members that are added to your data source will not automatically appear in any group. You always have to add them manually the first time they appear in the data source.
Think of sets as special kinds of filters that enable you to share findings made in one worksheet across other worksheets in your workbook. Or, perhaps you want to create an exception report that only displays records that meet specific criteria. Sets can be created in several different ways:
Creating a set by selecting marks in a view is fast and intuitive. Below diagram shows a scatter plot that is comparing profit and shipping cost. If you want to create a set that includes low profit items, hold the left mouse button down and draw a box around the marks you want to save. This will automatically open the tooltips.
Diagram of selecting marks to create a set :
Selecting the create set menu option exposes the dialogue box in Below diagram
Diagram of Editing fields included in a set :
If you want to exclude a category from the set, hovering the mouse over the category header exposes a red (x), which if selected removes the category field from the set. Similarly, if you want to remove specific records, you could do that by pointing and clicking on the same control appearing in the row. For now, keep all dimensions and measures in this set. In addition, you can rename the set calling it Low Profit Set. Clicking the OK button adds a new shelf below the measures shelf that includes this set. You can also use the set in other worksheets within this workbook. Below diagram shows different ways in which the set could be applied.
Diagram of Applying sets in different worksheets :
The time series on the left displays a record count and profit dollars for one year of sales. By dragging the low profit set to the filter shelf, the view will change to reflect only the records included in the set. The middle view in the below diagram shows the result. Notice the record count is much smaller and the profit pane has been filtered as well. Another way you could apply the set filter would be to double-click the low-profit orders set on the set shelf. This option produces the visualization on the far right of below diagram. The items that aren’t in the low-profit set are grey and the low-profit orders are blue.
[Related Article - Building Interactive Tableau Dashboards]
It is also possible to create a set by right-clicking on a field displayed in the dimension shelf and selecting the Create Set option. This will expose the dialogue box in which you can apply filters manually or via calculations
What if you want to create an exception report that only displays records that meet specific criteria? This can be achieved by joining two different sets in combination. You can see this in the following example and then use it to filter a chart. The desired combination set includes only order line detail for sales that are greater than one thousand dollars having profit ratios less than three per cent. The steps required to create this combination set are:
Diagram of making the sales set :
Superstore includes information on each order down to each item included in the order. You want to display each order-row that is over one thousand dollars but less than three per cent profit ratio. To enable this combination set, create a calculated field that uniquely combines order ID and row ID. Create a new field called Order-RowID by making a calculated field that concatenates the Order ID field and a row ID field. This can be done by using the following formula syntax: [Order ID] +”-” + [Row ID]. You will learn more about calculated values in Chapter 4.
Below diagram shows how the settings dialogue box is exposed by right-clicking on the calculated field you have just created for the combination of order and row ID. On the General tab, you will select all records. Using the condition tab you can choose the sales field for the sum of sales exceeding one thousand dollars. Name the set (Sales > $ 1K) and click the OK button.
[Related Article - Tableau Careers And Salaries]
Next, you can create a set that will include only items with a profit ratio of less than three per cent. Below diagram shows the condition which the dialogue box has exposed after right-clicking on the Order-RowID field and selecting all records from the general tab, then defining the profit ratio limit.
After defining these sets you can now create a combination set. You do this by pointing at the set for sales over one thousand dollars, right-clicking, and selecting the Create Combined Set menu option. Below diagram shows the dialogue box that is displayed.
Diagram of the low-profit set condition defined :
Diagram of Combination set dialog box :
The (Sales > $ 1K) set is already in the left set drop-down menu. The right drop-down menu was initially empty. Select the (Profit < 3%) set and the Radio button for the shared member's option. Then click the OK button. This will generate another filter set that is the combination of the intersection of both sets. Below diagram shows a bar chart that uses the combination set in the view.
Notice that the set option for displaying items in or out of the combination set has been selected. To make this chart easier to view, the color shelf has been edited to display items with profit ratios less than three percent using orange and over three percent by using blue. Each bar is labelled with the sum of sales and profit ratio— providing visual confirmation that the data has been properly filtered by the combination set.
Free Demo for Corporate & Online Trainings.