The Goal
When I was working through the second iteration of my MakeoverMonday Submission, I decided that I wanted to create a chart similar to the one Andy Kriebel built below
This is a simple grid that shows all Participants and Weeks, and highlights which weeks they completed. What I really liked about this chart was any person can easily see which weeks they completed, but I thought it was a little more difficult to see the weeks missing. So I wanted to create something very similar to this, but give the user the ability to "Highlight" Completed OR Missing Weeks.
The Data
The data was pretty straightforward. For most people, there was one record per week, corresponding to their submission. However, on occasion, there were multiple records in the same week for one person (Me mostly, I iterate a lot). In addition, there was "Missing" data. If someone didn't submit for a particular week, there was no record for that individual.
The Process
After looking at the data, I knew I had 2 choices
1. Create a "Weeks" table in Excel, and do a Cartesian Join to the MakeoverMonday table so that every person had a record for each week.
2. Do it all in Tableau and leverage Table Calcs
I decided to go with the latter because I felt it was more manageable, and anytime I have the opportunity to do something in Tableau, I do.
Onto Tableau
The Setup
Step 1 was creating the barebones Grid chart so I could see what I was working with.
Notice how on "Missing" weeks, there isn't a Circle, it is just a blank space. Normally this would be a problem, but Tableau allows us to enable Densification on Crosstabs by leveraging the Cross product of the two dimensions. All we have to do is introduce a Table calc to instantiate the mark. Luckily for me, I wanted to create a "Missing Weeks" Column, so I simply adjusted that to be a table Calc.
Here what I have done is taken the FIXED LoD of COUNTD Weeks (This will give me the maximum weeks in the dataset), aggregated it to MIN and Subtracted the Total Submissions (Simply COUNTD on Weeks). Finally, I wrap it in a WINDOW_MIN(). By adding this Table Calc, I'm able to create a Mark for every missing week, notice in the chart above how every mark is now a circle. (NOTE* I should have eliminated the Fixed LoD and the Window Min, and instead went with a WINDOW_COUNT() since my vizLoD matched the Level of Detail I was computing on).
Step 2, Now that I have a "Mark" for each person every week, I needed to create a method that allowed the user to switch highlights between Completed and Missing Weeks. To do this, I created a very basic Parameter with two options.
Once I had the Parameter created, I needed to create a calc that "Switched" colors based on selection. Could by simple enough, but I had to keep in mind that I had missing data, so I had to be careful not to create a calc that messed up my densification. (NOTE* there are a couple of different ways you can create 2 color charts in Tableau, one is by creating a Dim and having Discrete colors, the other is using a Stepped Diverging Color, I went with the latter).
IF [Highlight] = 'Completed Weeks' AND NOT ISNULL(SUM([Number of Records])) THEN 1
ELSEIF [Highlight] = 'Completed Weeks' AND ISNULL(SUM([Number of Records])) THEN -1
ELSEIF [Highlight] = 'Missing Weeks' AND ISNULL(SUM([Number of Records])) THEN 1
ELSEIF [Highlight] = 'Missing Weeks' AND NOT ISNULL(SUM([Number of Records])) THEN -1
END
What I have done here is setup a calc that will give each "Mark" a value of either 1 OR -1 depending on the Parameter and whether or not the Week is Missing (NULL). When the Parameter is set to Completed Weeks, The Marks with "Completed Weeks" are designated a 1, while "Missing Weeks" are designated a -1, the opposite is true when the Parameter is set to "Missing Weeks". After creating the Calc, I placed it on the Color Shelf and setup a Diverging Pallet with Two Steps.
Now my user can Switch Highlights between Completed and Missing Weeks
Now that I had that done, it was onto the next step, Filtering.
Step 3, I wanted to give the user the ability to Filter based on a Name search. This is normally pretty simple, we simply drag name on the Filter shelf and make it a Wildcard search. The problem is, we can't do that here because we are leveraging Table Calcs (And I wanted a non case sensitive search).
To get around this, I first setup a Blank String Parameters and called it "Name".
Next I created a calculated field that I could use on the Filter shelf.
To learn more, download and interact with the dashboard below!
0 Comment