Where do you want to go?
Get Started
Online Courses

US Choropleth Using QlikView Pivot Table

by Ray Frye, on Jun 26, 2015 11:24:59 AM

We had some conversations internally around the idea that it might be easier to read a choropleth if the regions on it (in this case US states) were all a uniform size and shape. It is possible to do this using QlikView's standard pivot table. Here I'll explain how to set this up and provide a sample application.

First, we start with a data set. We'll need 2 tables. The first is our [State Data] that has the data we want to compare for each state. The other table, [Map Grid], has the row and column information that will tell us where to put each state on the map. I used a relatively simple data set for my [State Data] table - just a list of states and a random number between 0 and 1 to make it simple to use the ColorMix1 function on the front end. The [Map Grid] table will have 3 columns - the row, column and state name that will relate to our [State Data] table. We'll still need to store rows for the blank spaces between states because otherwise there isn't actually any data to show - there needs to be something there to represent a space.

Once you have your data set, you'll load that into QlikView. The [State Data] table and [Map Grid] table should be related on the state field.

US Choropleth - script US Choropleth - data model

On the front end, create a pivot table and add the Row and Column from the [Map Grid] table as dimensions. Set the background color and text color properties to argb(0,0,0,0) - this will essentially hide the values Row and Column values from the user as they are not useful anyway.

US Choropleth - dim background color US Choropleth - dim text color

Go to the Presentation tab and turn on the always fully expanded option. We're using a pivot table, but we don't actually want the user to be able to use it like a pivot table.

US Choropleth - always fully expanded

In your expressions, add the state name field. I've set it up so the expression is actually only({1} State), so the grid always shows all states. Selections will be highlighted with colors instead of hiding the state in the chart.

US Choropleth - expressions

In the background and text color properties, we're going to add an expression that will color the cells based on the value of the metric being measured. In the background color, first we're checking to see if there is a valid state in the cell - if there's not, we'll just use that argb(0,0,0,0) value again to make it transparent. If there is a state name, we'll use the ColorMix1 function to return a color between my c_RiskLight and c_RiskDark variables (light blue and dark blue) based on the value of Rating 1, which from the first step is a random value between 0 and 1. The text color is just going to check for the value of Rating 1 and return white if it is greater than .5 and black if it is less than .5. This will make it easier to see the state name against the different colored backgrounds.

US Choropleth - background color US Choropleth - text color

At this point, you have a standard looking pivot table, but it doesn't look much like a map. You'll want to take the Column dimension and drag that up towards the top over the State Name expression.

US Choropleth - unpivottedUS Choropleth - pivotingUS Choropleth - pivoted

There's a few touch ups we can do to make this look a little nicer. Make the column width on the Row and Column dimensions as small as you can, turn off drop down select, and make the labels just blank spaces. On the style tab, click the background button on the bottom of the window and change the transparency to 100% (not the cell background color transparency - this will turn off all coloring on your states). Soften the cell borders between states by changing the cell borders transparency on the Style tab to 100% (or whatever you're comfortable with). On the presentation tab, wrap the cell text to 2 or 3 lines and make the Data (Text) alignment centered for the State Name expression. On the Sort tab, un-check all boxes for both the row and column dimensions so when you select a state, it stays in place relative to the other states.

When you're done, you should have something that looks like this:

US Choropleth - complete

You can see this on Qlik Branch here or git here.




Subscribe to Updates