Color Mixology 101 & Heat Maps

I used a great QV feature for the first time this week – the Color Mix Wizard. Insofar as it is indeed a Wizard, there’s not a lot you need to know to use it, but I’ll walk through the steps I took…

The Grid Chart

The objective of this exercise was to come up with a visualization for looking at the number of survey respondents based on their answers to two different questions. My first attempt was to use a scatter plot, but that failed miserably since my dimensions (the x- and y-axes) were not continuous numbers, but rather ordinal values. Even when I encoded those ordinals as integers, the results were not what I was looking for.

SO…enter the grid chart.

Having worked through this example helped me clearly articulate the use case for the grid chart – basically, it is a version of a scatter chart that works for categorical values, be they nominal or ordinal. Nominal values are labels like “US, UK, FR” or “Finance, Sales, Operations.” Ordinal values are also categories, but can be arranged in a ranked order like “Under 25, 25-30, 30-35”. (for more info check out this Wikipedia article Level of measurement)

Here is my grid chart with two dimensions and bare-bones formatting:

Try as I might, I could find no simple way to make each horizontal row the same color…sigh…

Grid Meets Heat

So that grid chart tells me relatively how many people responded to each combination of question 1 and question 2. And it also provides some correlation information – for example, there are a large number of respondents falling into x-category: “18-25,” y-category: “4-6 hours.” This information is useful in and of itself, but I also wanted to compare it to their responses to a third question – a simple YES/NO question.

I could have opted for the pie chart style (from the Style tab) … but I’m already breaking Stephen Few rules by encoding magnitude as the radii of the bubbles 🙂 … best not make it worse with pies. Moreover, I personally don’t care for the aesthetic of the pie grid. Regardless, had I chosen this route, I could have simply used the third question as a third dimension and the pie chart would have proportioned two “slices” of pie – one for YES and one for NO. And included a legend too.

Instead, I decided to use a varying color saturation on grid bubbles. Since this will require a single number (vs. two categorical values – YES/NO) I needed a way to encode the survey responses to a single number. Hence I chose to take the ratio of YES responses (i.e. # of YES responses divided by # of ALL responses) which results in a floating point number between 0 and 1.0.

Now with a single number I can use it as a parameter to control color saturation. For example – I could simply use it as the alpha value (multiplying by 255 or similar), but I wanted finer control … especially at the “light” (faint) end of the scale. This is where the Color Wizard comes in!!

The Wizard (cue Black Sabbath)

First of all, it’s not obvious where to find this wizard…

  1. Expand the properties on the expression
  2. Click on Background Color to open the Expression Editor
  3. Go to the File Menu and you’ll see the Color Mix Wizard… menu item

Take some time to play with the wizard – it may take a few passes to get the effect you want. A couple of key parameters to explore:

Enhanced Colors – depending on the distribution of your data you may or may not want to select this option. It’s effect is analogous to a stereo enhancer for audio in that it takes the “middle” and pushes it out to both “sides.” Which in our case means taking the middle values and pushing them more towards to the upper and lower ends of the colors you’ve chosen.

Value Saturation (upper/lower) – again, your use case will dictate how to set this. If your data has long tails, you may want to set these limits so they group the outliers into a common color.

The Color Wizard will generate a piece of code that should look something like this:

=ColorMix1 ((rangemin(.8,rangemax([Legend Value] ,.1))-.1)/(.8-.1), ARGB(255, 242, 242, 242), ARGB(255, 128, 0, 0))

You can identify the Value Saturation range values (0.1 and 0.8) and the RGB values for the colors, so if you need to tweak the expression, you can do it directly on the code, instead of re-running the wizard.

NOTE: if you use Enhanced Colors you’ll get a much lengthier piece of code!

So now I have a true heat map, with the darker red bubbles indicating more YES responses to Question 3:

Finishing Touch – The Legend

Finally, it would be nice to have a more quantitative explanation of the heat levels besides the text: “darker means more YES responses.” So, a simple legend can be easily implemented in a straight table chart. This table will have just three columns:

  • the legend label – like “> 10%”
  • the legend value – 0.10, 0.20, etc.
  • a numeric expression (required to use the Chart object)

The expression really doesn’t matter because we’ll “white” it out (i.e. white text on white background).

Here is the load statement for the inline table:



Legend Label, Legend Value

< 10%, 0.1

20%, 0.2

30%, 0.3

40%, 0.4




After reloading the script, create a Chart object and select the Straight Table option. (I like Style = Table 1 for this example.) Choose Legend Label and Legend Value as the two dimensions. The expression column doesn’t matter, you can use something simple like only([Legend Value]) or sum([Legend Value]).

Now apply the colors…

  1. Expand the formatting options on the Legend Value dimension and edit the Background Color – you can either use the Color Mix Wizard again, or easier still, just copy the Expression from the Grid Chart object.
  2. Edit the Text Color option and paste in the same color expression from Background Color (this will render the text “invisible”)
  3. Go to the Expressions tab and expand the formatting options on the metric and edit the Text Color expression setting it to white() (assuming you’re using a white background)

That should do it! Resize the columns a little bit to balance the shape and you should have something that looks like this:

We Have Arrived

Finally, putting it all together: