2021: Week 2

Building on from Week 1's challenge, we are going to take your data prep skills on one step further. The next steps we are introducing this week are:

• Aggregation - changing the level of granularity of your data. The combination of the categorical fields often sets what each row represents so aggregating data changes this. In Tableau Prep this is different from how we aggregate in Tableau Desktop.
• Calculations - If the value or variable that you need to use isn't in your data set, you will often be able to create it from the other data fields you do have. 

As per last week, we've attached some help links that will teach you the techniques if you need a few nudges. One of the main challenges with Data Preparation is to think about not just what you want to do but the order you need to do those steps in. The challenge this week will be a good example of that to avoid repeating steps. Here's a post that might help you with your planning. 

Also, thank you to all those who posted their solutions, in all the different tools on Twitter and the community forums last week. Keep sharing those solutions, you are helping more people learn these important skills than you think!

Challenge

Challenge by: Carl Allchin

This week we are looking at the different Brands of bikes available in our stores. We need to understand what are the most popular sellers and do the customers of the different brands have the same experience to other customers. 

We are creating simple summaries this week to get a quick, tabular view of the answers. If you want to visualise the data to highlight those trends even more clearly, go for it! 

Input

One csv file of bike sales.

Requirements

• Input the data (help)
• Clean up the Model field to leave only the letters to represent the Brand of the bike (help)
• Workout the Order Value using Value per Bike and Quantity.
• Aggregate Value per Bike, Order Value and Quantity by Brand and Bike Type to form: (help) 
• Quantity Sold
• Order Value
• Average Value Sold per Brand, Type
• Calculate Days to ship by measuring the difference between when an order was placed and when it was shipped as 'Days to Ship' (help)
• Aggregate Order Value, Quantity and Days to Ship by Brand and Store to form:
• Total Quantity Sold
• Total Order Value
• Average Days to Ship
• Round any averaged values to one decimal place to make the values easier to read
• Output both data sets (help)

Output

Two files:

1. Sales by Brand and Type

5 Data Fields:

• Brand
• Bike Type
• Quantity Sold
• Order Value
• Avg Bike Value per Brand

15 Rows (16 including headers)

2. Sales by Brand and Store

5 Data Fields:

• Brand
• Store
• Total Quantity Sold
• Total Order Value
• Avg Days to Ship

25 Rows (26 including headers)

Here's our full output for comparison. Remember we don't care about the order of the columns or rows as Tableau Desktop will import them in whatever order they come in!

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!