At the end of my first week at the Data School, I had my first presentation on the material we learned. The topic I chose is Aggregations in Tableau Prep. This blog is going to walk you through the steps of aggregating data.
What is Tableau Prep?
Let's start with looking at Tableau Prep. What is it? What is it used for? Tableau Prep Builder is a tool to combine, shape, and clean your data to get it ready for analysis. It provides a modern approach to data preparation since it's easier, and faster than other data preparation tools since it's more user-friendly and automated.
What is Aggregation?
Connecting your data to Tableau Prep, you might need to aggregate some data. So what is aggregation? It is a mathematical function to lower the granularity (level of detail) in the data. It takes multiple values and converts them into a single value. For example, suppose there's a graduation data with each row representing the details of each student. In that case, aggregation can convert that data so that each row will represent data for each class. Therefore, we can say that students were 'grouped' together into classes and their details were aggregated into the related class's details.
Things to consider before starting to aggregate your data are:
- Grouping fields
Grouping fields decide the structure of the outcome, i.e., the level of the new granularity. In the graduation data, the new granularity of grades could be on the level of classes or on the level of high schools (given that there's data on more high schools). - Mathematical operations
Depending on how you want to aggregate your data, there are several mathematical operations to choose from. For instance, are you summing the number of shirts of each color for a total number of shirts? Are you taking the maximum hourly temperature reading over the course of a day and providing the daily max?
EXAMPLE
Data used: soap sales data
The following steps will complete the tasks (given in Preppin' Data) directly related to aggregation.
- Task 1: Sum up the sales for each combination of Store, Region and Quantity
- Task 2: Count the number of orders that has the combination of Store, Region and Quantity
TASK 1
Step 1.
When adding a step to the workflow choose Aggregate:
Step 2.
Drag the fields that make up the levels of the new granularity into the Grouped Fields pane (in this case Store, Region and Quantity). This will create a row for each combination of Store, Region and Quantity:
Step 3.
Drag the field that you want to aggregate into the Aggregated Fields pane (Sales). This will sum up the sales for each row:
Output:
TASK 2
Step 1. and 2. are the same as in TASK 1
Step 3.
Drag the field that you want to aggregate into the Aggregated Fields pane (Order ID) AND change the mathematical function to Count Distinct. This will count the number of orders for each row:
Output:
That's it. Now you can aggregate values in Tableau Prep!