2021: Week 2 - Solution

 

Solution by Tom Prowse and you can download the workflow here. 

It's week 2 of the 2021 Preppin' Data challenges and we are continuing on our fundamentals journey by focusing on Aggregating and Calculations. 

Again we were looking at Bike sales data and wanted to create some quick summaries to answer questions related to brand and store performance. 

Step 1 - Create Brand Field

The first step this week is to clean the Model field so that we only have the letters for each brand. 

The first step is to duplicate the Model field. We do this because we want both Model and Brand within our dataset so can't just clean the Model field without first duplicating it. This can be achieved by Right-Clicking and choosing Duplicate Field.

Once duplicated, we can use the Clean functionality within Tableau Prep to remove letters and punctuation. The Clean functions can be found by pressing the three dots, then selecting Clean: 

Now we have cleaned the Model field to create our Brand field the table looks like this: 

Step 2 - Calculate Order Value

The next step is to create the Order Value for each brand. This calculation involves multiplying Quantity and Value per Bike together using the following syntax:

Order Value 

[Quantity]*[Value per Bike]

As a result we now have a new field that containing our Order Value:

Step 3 - Aggregate Brand & Type 

For our first aggregate and output we need to have our Value per Bike, Order Value and Quantity by Brand and Type. 

This means that we need to:

Group By
- Brand
- Type

Aggregate
- Sum Order Value
- Sum Quantity (rename to Quantity Sold)
- Avg Value per Bike (rename to Avg Bike Value per Brand, Type)

The setup within the Aggregate tool looks like this: 

After the aggregation our table now looks like this: 

Step 4 - Round Values

The final step that's needed before the first output is to round the Avg Bike Value Sold per Brand, Type value to 1 decimal place. We are going to use the following calculation for this: 

Avg Bike Value Sold per Brand, Type 

ROUND([Avg Bike Value Sold per Brand, Type],1)

As a result our Avg Bike Value Sold per Brand, Type values now look like this: 

After completing the rounding, we are ready to output our data for the first output. This should look like this: 

Sales by Brand and Type

Step 5 - Days to Ship Calculation

We are going to go back in our workflow now and create a new branch so that we can create the 2nd output. From the step where we created the Order Value field, we can create a new step on another branch (just press the + on the right side of the step). This will separate the workflow and allow us to use an additional output at the end. 

On the new branch we want to calculate how many days it takes to ship each bike. To calculate this we are going to use the following: 

Days to Ship 

DATEDIFF('day',[Order Date],[Shipping Date])

As a result we now have a new field with the total amount of days between the order and ship date:

Step 6 - Aggregate Brand & Store

The next aggregation that we need to do involves the following: 

Group By 
- Brand
- Store

Aggregate
- Sum Order Value (rename to Total Order Value)
- Sum Quantity (rename to Total Quantity Sold)
- Avg Days to Ship (rename to Avg Days to Ship)

The aggregation tool setup looks like this:

After the aggregation our date now looks like this: 

Step 7 - Round Avg Days to Ship

The final step this week is to round the Avg Days to Ship value to 1 decimal place. We are going to use a similar calculation as before: 

Avg Days to Ship 

ROUND([Avg Days to Ship],1)

We are then ready to output our second table:

Sales by Brand and Store

You can download both of the full outputs here. 

That's Week 2 complete - Congratulations!!

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!