During the NFL football season, I use Qlikview to breakdown fantasy football statistics, helping me make lineup decisions and player transactions. I know, another fantasy football geek. However, using the football stats in Qlikview has forced me to learn new tricks and techniques that I often apply to my professional work.
Back to the example. I often need to compare player stats to determine which players I’d like to add to my roster and which players I want to drop from my team. Each week of the NFL season, there are hundreds of players that I need to evaluate. This includes comparing stats of free agent players to those players on my team.
The method to compare players is fairly simple. I have a basic straight table chart and I use a single dimension, calculating stat totals for each player (points, rushing attempts, receiving catches, receiving targets, total offensive snaps). This works fine to help me make the player decisions, but it has always been a bit clumsy in how I can visually review what I’m looking for. That is, until I figured out a simple setting to illuminate the key information.
Getting right into the thick of the example, I have the following straight table chart, using NFL player as the dimension:
For this chart, I like to track five stats for each player, which helps me understand which players are getting the most work and are the most productive
The problem? I have to scan through the chart to see which players are actually available to add to my team (free agents). Also, I like to compare free agent stats against my own player stats to determine who I can drop off my team when I make a transaction.
Here is where the Qlikview dimension background color comes into play. First, I want to visually identify which players are available as free agents. I have a separate field in my data model that denotes whether a player is a free agent or not (FreeAgent = Yes/No indicator).
In the properties of the chart, I go into the “Dimensions” tab and expand the options under my used dimension, Player:
Select the “Background Color” option, then click on the “Edit” button, which takes you into the Edit Expression window for the dimensions Background Color.
Once here, I use the following formula:
=if(FreeAgent = 'Yes',yellow(),if(Team = 'Nirvana', LightGreen(),white()))
This formula is evaluating the dimension “Player” and will set the background color of the cell in the chart based on the values of these fields. The fields “FreeAgent” and “Team” are part of my existing data model and are associated with each Player. In this case:
If the Player is a free agent (i.e. FreeAgent = ‘Yes’), then the cell background for that player in the chart will be set to Yellow.
If the Player is on my team (i.e. Team = ‘Nirvana’), then the cell background color for that player in the chart will be set to Light Green.
All remaining players will remain with a cell background color of White.
Once I apply this update to the dimension background color, the chart will now look like this:
Now, it is easy to see available players (free agent players shaded in Yellow) and players already on my team (players shaded in Light Green). This makes it very easy to compare players and make decisions, without having to scan the chart and apply additional filters.
Qlikview has many settings that help you identify key pieces of information to make well-informed decisions. In this post, I demonstrated a very simple technique to visually identify key information in a given chart, conditionally formatting a dimensions background color.