2021: Week 43 - Is that the case?

 Challenge by: Beth Kairys

A couple of weeks ago, at the Tableau Prep User Group, co-leader Beth Kairys presented a brilliant workflow which covers a lot of the fundamentals of data prep. Luckily, she agreed to allow us to turn it into a Preppin' Data challenge, thanks Beth! If you missed the session or want a sneaky peak at the solution, then click here to watch the user group recording.

The purpose of the challenge is to get our cases data into a format where we can reproduce the following chart (which you're welcome to do as a bonus exercise too!)



Inputs

There are 3 inputs for this challenge:
  1. Business Unit A cases 

  2. Business Unit B cases 

  3. Risk Level lookup table 

Requirements

  • Input the data
  • From the Business Unit A Input, create a Date Lodged field
  • Use the lookup table to update the risk rating
  • Bring Business Unit A & B together
  • We want to classify each case in relation to the beginning of the quarter (01/10/21):
    • Opening cases = if the case was lodged before the beginning of the quarter   
    • New cases = if the case was lodged after the beginning of the quarter
  • In order to count cases closed/deferred within the quarter, we want to call out cases with a completed or deferred status
  • For each rating, we then want to count how many cases are within the above 4 classifications
  • We then want to create a field for Cases which will carry over into the next quarter
    • i.e. Opening Cases + New Cases - Completed Cases - Deferred Cases
  • Reshape the data to match the final output
  • Output the data

Output


  • 3 fields
    • Status
    • Cases
    • Rating
  • 15 rows (16 including headers)
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

2023: Week 1 The Data Source Bank

2023: Week 2 - International Bank Account Numbers

How to...Handle Free Text