2024: Week 52 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Split Nice or Naughty
After inputting the data we can then start to split out the names on the Naughty or Nice list and the order in which they are on the list.
First we want to split the List and Order from the File Paths field. We can do this using an automatic split or using a split on the space.
We can then rename split 1 to List and split 2 to List Order.
Step 2 - Index & Score
Next we want to create an index field by combing the File Path number and id.
Index
([List Order]*100)+[id]
From here we can then calculate a Nice and Naughty Score:
Nice Score
{FIXED [first_name]: SUM(IF [List] = 'Nice' THEN 1 ELSE 0 END)}
Naughty Score
{FIXED [first_name]: SUM(IF [List] = 'Naughty' THEN 1 ELSE 0 END)}
Then finally we can determine what list each name should be on based on their scores.
Nice or Naughty?
IF [Nice Score] > [Naughty Score] THEN 'Nice'
ELSEIF [Naughty Score] > [Nice Score] THEN 'Naughty'
ELSE 'TBC'
END
Then we can use the list order to determine what list they should be on in the scores are tied. First we want to find out what list the name appears on the latest.
Latest Decision
{ FIXED [first_name] : MAX([Index])}
Then using this we can calculate for the TBCs:
Naughty or Nice Tiebreak
IF [Naughty or Nice?] <> 'TBC' THEN [Naughty or Nice?]
ELSEIF [Latest Decision] = [Index] THEN [List] END
We can then create one row per person by using an aggregation where we group by first name and then find the Max of the tiebreaker field:
After the aggregation we are ready to output our table:
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!
