2024: Week 48 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Ranking Field & Tie Breaker
The first task is to create a Ranking field for each of the different sports. After inputting the table for each sport then we want to create a separate branch for each of them (4 in total). Within each of these branches we want to rename the corresponding field to ranking field as follows:
NBA Results
WWins = Ranking Field
NFL Results
WWins = Ranking Field
Premier League
PtsPoints = Ranking Field
Rugby Aviva Premiership
PTS = Ranking Field
Once we have renamed each of these fields we can then create a tie breaker for each branch.
NBA Results
First we want to rename the GBGames behind field to Tie Breaker 1 and then use the first part of ConfConference record field as our Tie Breaker 2. In order to get just the first part of the field we can use a Split calculation:
Tie Breaker 2
TRIM( SPLIT( [ConfConference record], "-", 1 ) )
Next we want to keep only the Team, Ranking Field, Tie Breaker 1, Tie Breaker 2 fields.
NFL Results
Next we want to repeat the same process with the NFL results branch, however this time we want to create the Tie Breaker 1 field using this calculation:
Tie Breaker 1
[PFPoints for]-[PAPoints against]
Then we can rename the PFPoints For to Tie Breaker 2.
Again we want to only keep the Team, Ranking Field, Tie Breaker 1, and Tie Breaker 2 fields:
Premier League Results
Next is the Premier League branch. This time we just need to rename the WWins field to Tie Breaker 1 and the GF Goals scored field to Tie Breaker 2.
We can then remove any fields so we only have Club, Ranking Field, Tie Breaker 1, and Tie Breaker 2 remaining.
Rugby Aviva Premiership
Finally, we can do the same with the Ruby branch. For this we want to rename W to Tie Breaker 1 and PD for Tie Breaker 2. Again, we want to only keep the 4 fields as with the previous branches:
Now that we have each of our tables in the same format, we can bring these together using a union step. This will 'stack' each of the tables on top of each other and create a single table. We will need to make sure that all fields are aligned, so we will need to manually make sure Team and Club are seen as the same field.
Step 2 - Create Sport Field
Now we have the tables together into a single view, we can use the Table Names field to identify which sport each row is associated with. For this we want to split apart the last part of the field:
Then we can remove the 'Results' from the values:
Sport
REPLACE([Table Names - Split 1],' Results','')
Our table should now look like this:
Step 3 - Z Scores & Rank
The next part of the challenge is to calculate the Z-Scores across the different sports so that we can compare them.
First we need to rank within each sport:
Next we can calculate the Mean and Standard Deviation using LODs:
Mean
Standard Deviation
Then finally the Z-Score:
Z-Score
([Ranking Field]-[Mean])
/
[Standard Deviation]
Once we have calculated the Z-Score we can rank the table again based on the number of teams within each league:
Number of Teams
After this there are a couple of other rank calculations:
Sports Specific Percentile Rank
1 -
([Rank within Sport]
/
[Number of Teams])
And also the Cross Sports Rank:
Cross Sports Rank
Then we can remove any additional fields so that the table looks like this:
At this stage we are ready to output our first table:
Step 4 - Output 2
For the second output we need to create a new aggregate step where we will group by Sport and find the Avg of the Cross Sport Rank:
We can then rename the Cross Sport Rank field and output our second 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!
