2021: Week 3 - Solution

 

Solution by Tom Prowse and you can download our full workflow here. 

Week 3 was the third instalment of our Tableau Prep fundamentals challenges to kick off 2021. This week we were looking at the sales of bike accessories in our stores with a focus on pivoting and aggregation. 

Step 1 - Wildcard Union Input

Our first task this week is to input all of the sheets from the Excel document. You could bring all of these in individually and then use the union tool to combine them. However, there is an easier way of doing with functionality within the input step.

By choosing 'Multiple Files' and then 'Wildcard Union' you can then make some selections about what files or sheets you want to be included. There is the ability to look at multiple files within a folder, but for our use case we want to include multiple sheets from the same file. 

As we want to include all of the worksheets, we can leave the 'Matching Pattern' blank, however if, for example, we wanted to include all sheets starting with an 'L' we could use the * as our wildcard. Using 'L*' would return all of the sheets starting with L, so in our case it would input London and Leeds.

For this scenario we want to have the wildcard union setup like this: 

As a result of the wildcard union we now have a table that should look like this: 

Notice, how all of the different sheets have been 'stacked' on top of each other, and distinguished via the Table Names field. 

Step 2 - Pivot Data

We are now going to introduce the pivot tool to our workflow. This will allow use to change the shape of the data by moving values from columns to rows or vice versa. 

For this challenge we want to bring all of the products and customer types into a single column, therefore using the columns to rows pivot. 

We then want to include all of the New or Existing columns in the pivot. There are a couple of ways to do this: 

1. Drag and Drop  - You can select each (or multiple) columns then drag and drop them into the Pivoted Fields section. 

2. Wildcard Pivot - By pressing the 'Use Wildcard Search to Pivot', you are able to use a matching pattern to bring in various different fields all at once. In our case, all of our fields have a '-' in them, therefore we can use this to bring all of the New/Existing fields all in one go!

Our pivot setup looks like this: 

As a result of the pivot we need to tidy some of the fields so that they are easier to identify going forwards. We are going to update the following: 

1. Automatic Split on 'Pivot1 Names'. 

This will split our customer type and product fields. 

2. Rename 'Pivot1 Names - Split 1' to 'Customer Type' 

3. Rename 'Pivot1 Names - Split 2' to 'Product' 

4. Rename 'Pivot1 Values' to 'Products Sold'

5. Remove 'Pivot1 Names'

Our data should now look like this with our Customer Types and Products each in a single column: 

Step 3 - Create Date Quarter

Next, we want to convert our date field into it's corresponding Quarter. We can do this with Tableau Prep's native functionality, by pressing the three dots then selecting 'Convert Dates'. From here we select Quarter Number and then rename the field to 'Quarter'. 

Our date field has now become our Quarter field: 

Step 4 - Aggregate Data

The final part of this week's challenge is to aggregate our data in two different ways to answer the couple of different questions. 

First we want to find how many products sold by Product and Quarter therefore our aggregation looks like this: 

As a result of this aggregate we are left with this output: 

On a separate branch, we are going to create another aggregation tool but this time we want to find how many products have been sold for each Store, Customer Type & Product combination. The aggregation setup looks like this: 

Our second output should look like this: 

Both of the full outputs can be downloaded here. 

That's it! Congratulations, that's Week 3 completed!!

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!