2020: Week 49 - Solution
This week's challenge is all about the NBA and is inspired by Tableau Zen Master Rob Radburn who created a great viz on Formula 1 data. We decided to take his idea and explore the 2018/19 NBA season so that we could then go on and create a similar visualisation!
Step 1 - Input all of the data
The first step this week is to input all of our data tables. We could do this individually, however this will take a long time and not be very dynamic. Therefore, we use the Wildcard Union in the input tool, and include all of the sheets from the file:
We then need to tidy up some of the fields by renaming and removing some of them. Our data should look something like this:
Step 2 - Who Won?
The next step is to determine who won each of the games using the following calculations:
H Win
INT([H PTS] > [V PTS])
V Win
INT([H PTS] > [V PTS])
Each of these calculations returns a 1 (win) or 0 (loss) as to whether the home/visitor team won/lost. We also want to use a Rank calculation to give each of our games an ID which will help us later in the workflow:
Game ID
Our data should now look like this:
Step 3 - Game Order
Next we want to split the workflow into two sections, 1 for home games and 1 for away. But first we need use an aggregate tool to make a list of each of the teams that are in the league:
We can then join back to our original data source where we split for home and away teams.
Home Games
The join condition here is:
Then we can remove V Win, Visitor/Neutral, V PTS, Home/Neutral, H PTS fields, then rename H Win to Win.
Away Games
The join condition here is:
Then we can remove H Win, Visitor/Neutral, V PTS, Home/Neutral, H PTS fields, then rename V Win to Win.
Now that both our Home/Away games are in the same structure we can use the union tool to stack these on top of each other.
We now use a rank calculation to determine the game order for each team. The calculation looks like this:
Game Number per Team
After removing any extra fields our table looks like this:
Step 4 - Cumulative Wins
We now want to create a running total so we can calculate the cumulative wins at each stage for each team.
To do this we will need to create a self-join therefore we create a new step, remove to Win field and then join back to our original workflow using these join conditions:
Our workflow for this part looks like this:
We can then use an aggregation tool to create the running total with the following setup:
Step 5 - Ranking
The final step this week is to rank each of the teams to see how they compare with each other. To do this we use this ranking calculation:
After this calculation we have our desired output:
The full output can be downloaded 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, @JonathanAllenby & @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!
