You’re Rank! And soooo Dense.

A few weeks ago Ben Jones of Tableau wrote a great blog post about how to create small multiple maps.  I’ve long been a fan of small multiples and their ability to convey a lot of information and insight in a smart use of space.  I had just never considered using them with maps.

In reading through Ben’s article, though, the only thing that bothered me was the need to hard code the top 9 incident types and then assign them the appropriate row and column number to build out the trellis. For a one-off viz with a specific story to tell, hard coding values works just fine. But since most of my visualizations are dynamic and interactive, I felt there had to be another way. I’ve also been burned in the past when I forgot to update a hard-coded value before sending out a revision of a dashboard.  Nothing like a little shame to spur a desire for improvement.

Figuring Out How to Rank the Incidents

As Ben already showed, getting a rank of the incidents based on the number of records is very easy.  You get the same result whether you do a descending sort using a bar chart…

or even a map.

In order to build out the small multiples (aka trellis chart), though, I need to know the rank of each incident type.  I need to know that Severe Storms is #1, that Hurricane is #3, etc.  Since I know that both of the views are sorting the incidents based on the total sum of Number of Records, that tells me that I need to first calculate/derive the total number of records inside of each map.  Given the granularity of the map vs. the bar chart (where no state or county data is present), I’ll need a window sum vs. a regular sum.

If I place that into the view, set it to discrete (so I can place it next to the Incident Type pill), and change it’s Compute Using to use all of the available fields (Incident Type, State and Declared County) but restarting every Incident Type, I end up with the correct total record count for each incident.  Now I just need to figure out how to derive the rank from this total record count.

RANK_DENSE to the rescue

My initial thought was to use a simple Index or Rank function to come up with this number.

However, no matter what I do in the Compute Using options, I never end up with the right answer.  I figure that the level of detail inside of each map is messing things up.  I’m sure people far smarter than me can explain the precise “why” behind this behavior (and I hope they do), but all I know is that I’m not getting the answer I need no matter what I do with the Rank function.

So I start looking at the other available functions, to see if anything else might do the trick.  That’s when I come across the RANK_DENSE function, one I recall hearing about at the TC13 conference but have never had a need to use (let alone any of the other RANK functions, since the original INDEX function has served me so well up to this point).  Given that I am feeling rather dense, being unable to figure this sorting thing out, I take this as a sign that I should give that one a try.  I also figure that the “density” of the data inside of the map is the culprit behind my inability to get a proper sort using the vanilla RANK function.

Lo and behold, when I place the RANK_DENSE field into the view and set its Compute Using to use all of the available fields (Incident Type, State and Declared County), while keeping the # of Incidents Compute Using set the way I had before (using all of the available fields but restarting every Incident Type), I get the answer I wanted.  The incidents are now sorted in descending order based on the total number of records inside of each map.  Fan-f’in-tastic!

Building the Small Multiple / Trellis View

Now all that is left to do is to use the Rank of each incident to determine its placement inside of the trellis chart, i.e. which row and column would it occupy.  Since Ben had originally set up a 3×3 view, I decide to stick with that as well.  You could create more multiples (e.g. 4×4, 5×5, etc.) but you’d have to decide whether your audience (and the medium they’d use for viewing your charts) would be able to get anything out of these increasingly smaller maps.

I set up two fields (similar to what Ben did), one for the Rows and another for the Columns, using the Rank to determine which row and column should be assigned to each map.

Placing the Column field on the Column shelf and the Row field on the Row shelf (along with adding Rank to the filter shelf and selecting 1-9) creates the small multiple view you see below.  I then just add the Incident Type and # of Incidents onto the Label shelf and tweak the settings so that they only show up once, roughly in the middle of each map.  I’m sure there are more elegant ways of doing this, but this seems good enough for this exercise. 🙂

Now the small multiples are set up dynamically, which means that if you apply any filters to the view (e.g. change the years), the Top 9 will update accordingly vs. being hard-coded based on your initial sort.

Thanks for reading.  The final workbook can be viewed below.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s