2020: Week 8 Solution
This week we focused on the qualities and functionalities that Tableau Prep has to offer with a 'clicks only' challenge, where no typed calculations were allowed. Throughout this challenge we are going to be using unions, joins, pivots and aggregations, so it will be a great introduction to some of Tableau Prep features.
For the challenge we are going to focus on pulling together multiple weeks worth of data, from different spreadsheets and turning this into a useful datasource. It was split into two outputs:
- Week's records where profit expectations have been exceeded.
- Week's records where budgets have not been reached
Output 1 - Profits
Input Data
The first task is to input the correct data so that we can start to solve the challenge. This week we are given multiple worksheets within a single Excel document, however for the first part of this challenge we only want to focus on the Weekly Sales data. Each of these sheets have the same naming convention, therefore we can use a wildcard union within the input tool, to input only the weekly sales. The setup would look like this:
Weekly Sales per Product
Now we've input all of the required worksheets, we need to calculate the weekly sales value and volume for each product. The first step will be to use the 'merge fields' functionality to bring together the Value and Volume columns that haven't come together nicely due to the field names being different.
As a result of the union, we can also rename the 'Table Names' field to 'week' as this contains the week number that our sales were made in.
Once we have cleaned both the weeks, value and volume fields, we are ready to use an Aggregation tool to calculate the weekly totals for value and volume. With the aggregation we want to group by week and type, then sum the sales value and sales volume. The setup will look like this:
Join Profit Table
The next step will be to use the Budget worksheet to extract the table relating to Profits. If we bring the budget table into the view, then this won't work as it looks a mess with the title and unfamiliar table structures. However, if we use the 'Data Interpreter', Tableau prep automatically identifies that there are multiple tables within the sheet and helps us by splitting these out.
We now have two new tables that can be used the the view. These are names Budget C3:F19 & Budget C22:G26, and the letters and numbers refer to where they are located within the Excel sheet. As these have been split, we can now bring in the 'Budget C3:F19' table as our Profit information.
Before joining the Sales and Profit tables, we need to clean some fields so that they have a common join condition. Within the Sales table, we need to use the Clean functionalities to Remove Letters and Trim Spaces from the Week field, and then change the data type to a whole number.
Within the Profit Table, we need to use a Custom Split to split off the week number in the week field. Currently, the week field has the structure yyyy_w, therefore we can use a custom split to split off the last field after the '_'.
We can now remove the original week field, then rename the newly split field 'Week' and change this data type to a number.
Now we are ready to join the Sales and Profit tables together using a Join Tool. We are going to use the following join conditions:
- Type = Type
- Week = Week
- Profit Min Sales Value <= Sales Value
- Profit Min Sales Volume <= Sales Volume
By joining on the profit value/volume and sales value/volume, then using the less than or equal function, this acts as a filter and removes any fields that haven't met their profit expectations.
The final step is to remove any unwanted fields and our first output should look like this:
Output 2 - Budgets
Input
Now we have calculated the profits we can turn our attention to the Budgets table. As we have already used the data interpreter, we can use the second table that Prep has created as our Budgets table.
Clean & Shape
Our first steps will be to clean the budgets table so that we can then join this onto our sales table. However, the budget table is in a completely different structure to the sales table, therefore we will need to manipulate this before joining.
The first step will be to clean the Type field so that only 'Bar' or 'Liquid' remains, and is the same as the sales table. To do this, we can use the clean functionality again, but this time we can Remove Number and Remove Punctuation from the type field.
Next we want to want to change the shape of the data, so that instead of the weeks being in separate columns, we want to bring this all into a single column. Therefore, we can use a Columns to Rows pivot to bring the three week columns, and put them into one.
Once we have all the weeks in one column, we can then use another pivot to bring the Value and Volume fields into separate rows. This time, instead of columns to rows, we are going to use a Rows to Columns pivot which will allow us split the Budget Value and Volume into two separate columns.
The final step before the join is to clean the week field so that we can again join nicely to the sales data. The week isn't in an individual row, like in the profit and sales, therefore we need to obtain the starting and ending week within the range.
To find the start/end of weeks, we can use an Automatic Split where Tableau Prep automatically splits both digits into separate columns. We can then rename these Start and End week.
We are now ready to join the budget tables with the sales tables. We need to split this into two joins as we are looking for both Value and Volume.
Value & Volume Join
To only return the sales value/volumes that have not reached their budgets, we need to set up the following conditions:
Value
- Type = Type
- Week >= Week Start
- Week <= Week End
- Sales Value < Budget Value
Volume
- Type = Type
- Week >= Week Start
- Week <= Week End
- Sales Volume < Budget Volume
Both of these joins act as a filter for the values that we want to remove. Both the week and the value/volume conditions allow us to limit what is returned in the join, and therefore we do not need to use a filter as a result.
Union Value & Volume
The final step that we need to take to get obtain our required budget output is to bring back the Value and Volume tables that we have just joined. As both of these tables contain the same field structure, we can use the Union tool to 'stack' these on top of each other, therefore creating one long table.
The only steps remaining are to remove any unwanted fields, and then finally use the clean functionality to make the Type field all lowercase.
Here's the budget output that was required:
