2023: Week 36 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Match Order
First we want to create a calculation to show the order that the matches occurred. To do this we can use a Rank calculation based on the Team, Player ID and Tournament Date:
Step 2 - Running Total
Next we want to calculate the number of games each player has been involved in. To do this we need to calculate a running total for each match.
To create a running total within Tableau Prep, we first need to do a self-join where we join our original data onto itself using an inner join where Team = Team, Player ID = Player ID, and Match Order <= Match Order:
After the join we can complete the running total by using an aggregate step where we group by Team, Player ID, Total Matches, Tournament Date, and Matches Played in Tournament, then sum the Matches Played in Tournament-1:
Then finally, to calculate the experience that each player has at the start of the tournament we can subtract the running total from the total matches:
Experience at beginning of Tournament
[Total Matches]-[Matches Played in Tournament-1]
After removing the additional fields we are ready to output our table that looks like this:
You can download the output 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!
