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


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!

Comments

  1. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.
    health and beauty care

    ReplyDelete

Post a comment

Popular posts from this blog

How to use... Custom SQL in Prep Builder

How to... Aggregate in Prep Builder

2019: Week 1