2024: Week 3 Performance Against Targets

 Created by: Carl Allchin   

The world is swimming in data and with so many sources everywhere, it's often on you to tie them together. Most data tools need to read from a single data so will use Unions, Joins and Logical Relationship models to tie them together. In this week's challenge we will introduce you to joining data sets together to prepare them for analysis. 

This week's challenge is to link together a Quarterly Sales Target data source (an Excel Workbook) with our original sales data (Week One output). Is Prep Air meeting its targets?

Input

In the Excel file this week there are four tables of data, one for each quarter this year.

Requirements

  • Input the outputs from 2024 Week 1 challenge
  • Input the new targets Excel sheet (Q1 - 4) 
  • Correct the Classes being incorrect as per last week
    • Economy to First
    • First Class to Economy
    • Business Class to Premium
    • Premium Economy to Business
  • Find the First Letter from each word in the Class to help with joining the Targets data to Sales data
  • Change the date to a month number 
  • Total up the sales at the level of:
    • Class
    • Month
  • Join the Targets data on to the Sales data (note - you should have 48 rows of data after the join)
  • Calculate the difference between the Sales and Target values per Class and Month
  • Output the data

Output


5 data fields:
  • Difference to Target
  • Date
  • Price
  • Class
  • Target

48 rows (49 including headers)

You can download the outputs from here.

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! 

Popular posts from this blog

2024: Week 1 - Prep Air's Flow Card

2023: Week 1 The Data Source Bank

How to...Handle Free Text