This blog is the second of a two part series of guest blogs. If you missed it, please check out part 1 written by Alberto on how to collect data from an API and format that data fit for Tableau, using Alteryx.
Naturally, this blog picks up from that stage of data prep, and starts to showcase specific metrics you can look at to build a live Premier League Dashboard tracker. I am delighted to welcome Joris Van Den Berg onto the blog to show you through his halftime heroes dashboard. Please find a little more about Joris below;
“It was my dream as a football, Tableau and data lover: a live Premier League Dashboard that updates daily with the latest results. The best matches, results and players automatically displayed. And this without having to upload a new version every week. In this blog this dream becomes reality. We show you how to create a live Premier League dashboard on Tableau Public, from start to finish.”
“My name is Joris van den Berg and I am 30 years old. I live in Amersfoort, a beautiful city in the middle of the Netherlands. I first came into contact with data during my bachelor social geography, when we had to make maps ourselves. Enthusiastic about maps and data, I decided to do a master in GIS (Geographical Information Systems) later on. Then, when I came across Tableau, I was hooked. Tableau was super easy to use, and you could make the most beautiful dashboards. With or without maps. I now work at The Information Lab Netherlands and started the Halftime Heroes together with colleagues Bibi and Lars. We combine our work with our passion and make dashboards about sports.”
Premier League Dashboard
Back to the Premier League dashboard. In the previous blog, you read how Alberto uses the Football API to pull in data. In this part, I describe some of the tricks I use in the Premier League dashboard. These tricks provide a weekly updated dashboard, including the match of the week, the teams in form and the players with the most assists and goals.
The trick for ‘live’ data in Tableau Public is to use Google Sheets. Live’ because Tableau Public only gets new data from Google Sheets on a daily basis. So it is not possible to put real live data in Tableau Public. But this way you at least have new data in Tableau Public every day. You can read more about how to link Tableau and Google Sheets in this blog.
(*The Google Sheets reader will likely no longer be supported by Tableau as of April 2023.)
Now that we have the data, it is time to create a dashboard. I will show you some handy tricks that I have used myself. Use these tricks to create your own Premier League dashboard, or recreate our dashboard for practice. Our Premier League dashboard is also available for download from Tableau Public, so feel free to use it.
Game of the Week
Let’s start with the match of the week. For how do I determine this if the data is updated automatically? Simple. I created a calculated field that looks at the results every week. For me, the match of the week is the match that scores the most goals. I have therefore worked this out in a calculated field.
We compare the maximum number of home and away goals scored in a match round with all individual matches. All matches with the most goals in that round are then marked TRUE. Matches with less goals are marked FALSE. We can then list the games with the most goals in each round by filtering on Game of the week = TRUE.
As you can see, sometimes there are two matches with the same number of goals. In addition, we want to have only the most recent match. This is solved by sorting the matches by date and using INDEX( ). By filtering on INDEX( ) 1, Tableau only shows the first row in the data. And voila, you have a calculation that shows the game with the most goals after each round of play.
In a similar way, I have also determined the shape of teams. This includes a calculated field that calculates how many games teams have won or lost. This results in the teams with the most games won or lost.
In the data, we have the field “Form”. It contains the names of the last matches, whether they were won (W), drawn (D) or lost (L). Using the calculated field we check how many W’s there are and subtract these from the total number of characters in the Form field. This leaves us with the number of matches won as a number.
Then we use this number again to make a list of the most winning teams. Using the same INDEX( ) trick we described earlier, we keep only the top team. Thus we have the team in shape. I use exactly the same way to determine the teams out of shape.
This last trick is the trickiest one. We want the players with the most goals and assists to be dynamically updated. However, to do this, we need to update the players’ pictures as well, and this is a challenge. We could solve this with custom shapes, but then we would have to save a picture for each player and link it to the name. That is very time-consuming. So we did it with URL actions and parameters.
Don’t spend too much time on this. It has given me a lot of headaches and Tableau has also announced Image Role at the Tableau Conference. Thanks to this new feature you can now have images render dynamically by having a field with the URL of the image in your data. And we have just that for the players.
In the end, the trick was to create a calculated field that calculates which player has scored the most goals or assists. The result of this calculation is the URL of the player in question. Then we create a parameter, which every time the dashboard opens, fills in the URL of the highest scoring player. Finally, we use this parameter in a URL action. This way, a web page with the highest scoring player opens every time. Let’s look at this step by step.
1. Calculate player with most goals
In this calculation we look at the field goals rank. If the goals rank is 1, i.e. the most scoring player, the calculation will return ‘Photo’. This is the field containing the URL of the player. Next, there is a fixed calculation in this field. This fills every line in the data with the URL of the highest scoring player. As a result, Tableau always performs the URL action with the URL of the highest scoring player.
2. Create parameter for most scoring player
In step 2, we create a parameter. We fill this parameter with the calculated field from step 1. At ‘Value when workbook opens:’ we also set this calculated field. The dashboard opens and the parameter is updated with the highest scoring player.
3. Add web page with image
Finally, we will add a webpage to our dashboard. Drag a web page into your dashboard. In the popup that appears, enter a URL. Enter the parameter from step 2. This way, the URL of the highest scoring player will be filled in as a webpage.
Repeat these steps for the top 2 and 3 most scoring players to build up your top 3 picture. Perform the same steps to show the players with the most assists on your dashboard.
Since we find that many users view the dashboards via Twitter and LinkedIn on their cell phones, we also created a mobile layout. I also have a handy tip for that, so that it looks neat. Tableau’s default phone layout is often not very nice. That’s why I create my own background image specifically for the cell phone. I add this image to my desktop version. Then I make the image floating and 1×1 pixel. Hide the image behind another floating object, and voila: now you have a special image for the background of your mobile layout. Finally, you place all the components of your dashboard into the new image.
Those were some tricks to create a live premier league dashboard! Hopefully you’ll be inspired and get started yourself. You can of course create dashboards for other leagues too. We are looking forward to seeing your beautiful creations. Tag Halftime Heroes on Twitter, Instagram or LinkedIn.
Thank you Joris for that insight into some calculations for finding, top teams, players and games as well as a few formatting tips and tricks to bring the visual to life with badges and players.
What an honour to have Alberto and Joris join to do an end to end process of collating, transforming and visualising premier league data. Shout out to them both, the information lab and halftime heroes.
What excited me most about Alberto & Joris collaboration was that it provides a full e2e automated solution!