Tile Grid Maps

Have you ever had one of those “of course I can do that in QlikView” moments? As a Qlik developer it’s not uncommon to find yourself struggling to create a chart or pull off a complex script that nobody asked for, but for some strange reason, you decided to tas a personal challenge. Well, I just had one of those moments.

25_28

I couple of days ago I was wandering around my usual blogs when I found a post by Alberto Cairo  where he talked about an interactive visualization created by the FiveThirtyEight team regarding the political preferences in the United States. [By the way, if you don’t follow those blogs you are missing a lot of great stuff!]

25_29

Besides being a stunning way to present What-If scenarios, I liked the usage of the Tile Grid Maps, which have become a pretty popular visualization lately. What is a Tile Grid Map, you ask? Well, think of it as a mix between a choropleth map and a table heat map. If you want to see more examples, you can visit Bloomberg, The Washington Post or The New York Times.25_30

It is true that most of the times we strive for more accurate charts but ironically, this technique’s boon is that it disregards the actual size of the regions and translates them to equal-sized shapes (in this case, squares) so it becomes easier to see even the smallest areas. Therefore, this solution is excellent when the geographic size is not the most important thing, but maintaining a loose spatial distribution is.

So anyways… In today’s tutorial we’ll build something like this:

25_27

Note: Most of the information presented here was taken from FiveThirtyEight’s post. I think those figures date from 2012 and since American politics isn’t amongst my favorite topics, I didn’t look for fresh data. Sorry about that.

As usual, you can find the QVW and data sources here.

Tutorial

1.- Let’s start by creating the skeleton of the map using a simple spreadsheet. You just need to create a matrix and arrange all the states as if you were playing Battleship.

25_01

[Some of my colleagues didn’t get that explanation… I’m feeling a little old now…]

2.- Create a new QlikView document and load this table using the CrossTable wizard (or just add the prefix manually).

25_02

3.- Load the rest of your data as usual.

25_03

4.- Create a Pivot Table using Row and Column as dimensions and only(State_ID) as expression.

5.- Go to the Presentation tab and select Always Fully Expanded.

25_04

6.- Drag and drop the Column column in order to create a cross table (that sounded weird).

25_05

7.- Select Wrap Cell Text in the Presentation tab. While there, adjust the alignment of the cells as well.

25_06

8.- Let’s get rid of those annoying NULLs by opening the Presentation tab (again) and changing the symbols in the lower left corner to spaces.

25_07

9.- Now, in order to clean our visualization perform the following actions:

  • Caption: Disable Show Caption
  • Layout: Disable Use Borders
  • Style: Set Cell Borders Transparency to 100%

25_08

10.- Since we are working with a Pivot Table, we cannot simply suppress the header row and hide the first column so it’s time for the nasty tricks. Activate the Design Grid (Ctrl + G) and right click on the header of the first column (the gray cell). Select the Custom Format Cell option and change the Background and Text colors to white.

25_09

11.- Perform the same steps with the first row (right click on any of the numbers) and the first column (right click on any of the letters). Your table should look like this:

25_10

12.- Now, let’s apply some color to our visualization. Open the script and create two variables containing the RGB codes for the democrats and republicans:

25_11

13.- The trick behind this map is the ColorMix2() function, so let’s take a moment to understand how it works. ColorMix2 returns a color gradient based on four parameters: a value from -1 to +1, the color for -1, the color for +1 and the color for 0. Since we already defined all these codes, the last 3 parameters are simple:

25_12

14.- However, the first one is a little trickier. We need to translate our democrat and republican percentages (0% – 100%) to a new scale ranging from -1 to 1. We can easily achieve this by applying a Min Max Normalization:

25_13

If you’re familiar with this technique, jump directly to step 15. For those of you who aren’t, let me explain it a little further. Let’s imagine there’s a state with 75% of democrats:

25_14

As you can see, we have two scales: the % of Democrats (0% > 100%) and ColorMix2 (-1 > 1). Since this state falls exactly in the middle between 50% and 100% in the first scale, it should be exactly between 0 and +1 in the second one, right? Let´s apply the formula using only the right–hand side of the scale as a reference:

25_15

That would be (.75 – .5) / (1 – .5) = +0.5, which looks OK according to our diagram.

So, in the end, if we apply this formula from the democrat and republican points of view, we’ll end up with something like this:

25_16

15.- Our ColorMix2 formula is now ready, so let’s use it as the Background Color for our expression:

25_17

25_18

16.- Our map should look like this. Pretty neat, right?

25_19

However, there is a little problem… If you select one state, the map practically disappears!

25_20

17.- We can avoid this behavior by telling the expression to “ignore” the selections made in certain fields (yep, Set Analysis). Change the table’s expression to this one:

25_21.png

Now, the maps remains intact even if you select a party or a state:

25_22

18.- Another improvement that I’d like to make is to color the “inactive” states in gray so we don’t lose the shape of the map when we make selections. Let’s add these lines to the  Background Color expression:

25_23

Since the length of the state would only be greater than 0 for the possible values (either selected or related), this conditional statement will color the “active” states with ColorMix2 and use gray for the rest.

25_24

19.- In order to reinforce this “Active / Inactive” visual cue, let’s hide the states’ name whenever it is unavailable:

25_25.png

25_26

20.- Add some extra labels and filters. Voilà, our Tile Grid Map is now ready!

25_27

 

What do you think about this type of visualizations? They surely sacrifice some things, but they offer other benefits. If you have any comments, be sure to post them in the section below. Thanks for reading. Until next time!

PS: Help me achieve my lifelong dream of having more <Followers> than <Following> in Twitter: @QlikFreak!

Advertisements

10 thoughts on “Tile Grid Maps

  1. Deepak says:

    When I select a state form the pivot table, the sorting comes in action. The view is distorted. Help!!

    • Try Sort > Text A -> Z in both the row and columns. Also you can try copying and pasting the object from the material and just changing the field. I think this issue may have to do with the set analysis bit of your expression. Are you “ignoring” other selections?

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s