Chapter 4. Slice and Dice – Grouping, Sorting, and Filtering Data

In this chapter, we will focus on slicing and dicing our data, covering the following recipes:

• Sorting the data

• Creating a custom hierarchy

• Grouping the data

• Creating bins to bucket our data

• Creating and using filters

• Creating and using sets

• Creating and using context filters

Introduction

Now that we have seen how to create various chart types in Tableau, it is time for us to focus on how to add more value to our analysis by using the various methods of slicing and dicing the data, filtering our data to only look at the relevant data points, categorizing our data into groups to make better sense, and so on.

Sorting our data

To make more sense of the data, we need to slice and dice the data in various ways and also look at it from various angles.

Being able to sort the data in ascending, descending or even in a custom manual order will help us put the data into a certain specific order and categorize it better.

Let's say we have around 1,000 customers in our data. Just showing the customers in a random order may not be of much use to us. However, if we sort the customers in descending order by profit, we can see the most profitable customers at the top and the lowest profitable customers at the bottom. However, sorting these customers in ascending order by profit will give us lowest profitable customers at the top followed by the most profitable customers at the bottom.

An example of a manual sort could be sorting the regions in a specific way so that it reads NEWS (North, East, West, and South).

There are various ways of sorting the data in Tableau. Let us look at the steps in the following recipe...

Creating a custom hierarchy

Using hierarchies is another way of slicing and dicing our data. This gives us the ability to be able to drill up and drill down into the data at various granularities. We have already seen an example of a default hierarchy in Tableau when we use any Date field.

When we get any Date field in either the Rows or Columns shelf, Tableau automatically aggregates it to the highest possible level. For example, if the date field includes multiple years, the default level is year. But, if the date field contains data for just one year but includes multiple months, then the default level is month.

In our data, if we get the Order Date field in the Rows shelf, then Tableau aggregates it at the Year level and gives us a field called YEAR(Order Date). It also gives us the + button (expand button) so that we can easily break down the view by year, quarter, month, and so on. Refer to the following image:

When Tableau identifies a field as a Date or Date/Time field, it creates the...

Grouping our data

Being able to group our data into higher categories is a very useful feature. The data that we connect to may not always have all the fields that are required for our analysis. There are times when we would have to go beyond what is available in the data and create some new fields either by doing some calculations or by using some default features. Imagine having data where we have state names, city names, and so on; but the region field has not been captured in the data. Now, there are going to be instances where we would need to do a region-level analysis. For example, we have state-wise sales but want to see how the sales are at a regional level. This information however, is not present in the data.

It's a good thing we have the Grouping feature in Tableau. We can create a group on the state names and club the relevant states into regions. Let us see how to create and use Groups in the following recipe.

Getting ready

We will continue working in our existing workbook and...

Creating bins to bucket our data

When we get fields such as sales, profit, discount, and many more in either the Rows or Columns shelf, it creates an axis. However, at times, it is important to organize these continuous measures into discrete groups rather than just showing the individual values for each and every data point. For example, let's say we have a field that holds the age of people ranging from 10 to 90. Rather than showing each and every age in the view, we can bin the individual ages into age groups such as 10 to 25, 26 to 40 and so on. This helps us get an idea of the distribution of the population. The range of this distribution is called a Class Interval. Further, in order to visualize this distribution of data, we use a graphical representation called Histogram which was first introduced by Karl Pearson.

Thus, in other words, binning is a process of dividing the entire range of quantitative values into a series of small intervals and then counting how many values fall into...

Creating and using filters

There are plenty of times when we want to narrow our focus on certain things in our view. This can be achieved by filtering the unnecessary data points. For example, we may have some products which are loss making and we want to focus only on those products or there are certain types of products that we want to use for our analysis. In such situations, we will use Filters in Tableau. We have a Filters shelf in Tableau and anything that needs to be filtered out will be placed on that shelf.

Let us see an example where we filter out the data.

Getting ready

For the following recipe, we will continue working in our existing Tableau workbook and we will now switch back to our Orders data from the Sample - Superstore.xlsx dataset.

How to do it…

1. Let us make sure that you have selected the Orders (Sample - Superstore) data source in the Data window. Once you've done that, create a new sheet and rename it to Filters.

2. Let us then drag Sub-Category from the Dimensions pane and...

Creating and using sets

The filters that we created in the previous recipe were created on the fly and are not available for later use, meaning each time we want to use the 12 product Sub-Categories that were shown as an output in the previous recipe, we will have to recreate the filters all over again. This becomes cumbersome and it would be great if we could save the output of the filtering conditions and simply drag the new field whenever we need to analyze it rather than repeating all the steps that we followed in the previous recipe. Luckily, we have Sets in Tableau to do this for us.

I like to call Sets as Pre-computed filters which can be used for the creating a sub-set of the data and/or saving the filters for later use.

Getting ready

To create Sets, we will continue working in the same workbook and use the Orders data from the Sample - Superstore.xlsx dataset for the following recipe.

How to do it…

1. Let us create a new sheet and rename it to Sets.

2. We will then on the dropdown or right...

Creating and using context filters

By default, each and every filter in Tableau will access all the records in our data source without taking into consideration the other filters. It means that these filters are computed independently of each other. However, we may want to set one or more categorical filter as context filters for the view.

For example, we have a Filter/Set, which gives us the Top five customers by Sales. Now this Set gives us the Top 5 customers from the entire data set. However, when we get another filter, let's say Region, then we expect Tableau to give us the Top five customers for the selected Region. But, because these filters are independent, Tableau still gives us the Top five customers from the entire data set and if it doesn't find a record of any customer from the Top five list in a particular region, then it will simply remove that name from the list. This is because Tableau doesn't understand that the Top five Filter/Set needs to be based on the output of Region...