Highlighting Redux – Alternate States to the Rescue!

The year was 2010 … Katy Perry and Lady Gaga topped the charts. Gas was $4.00 a gallon. A beer in Los Angeles cost five bucks. And dynamic highlighting of dimensional values in QlikView charts (a.k.a. brushing) needed a data island. AND I did a post on this topic: Highlighting a “special” dimension in bar charts.

Fast forward to Summer 2013 … okay, not much has really changed in the bigger picture but WOW is it easier to do brushing in QlikView. Actually, this is not really a new technique because I’m going to employ alternate state to do this trick which has been around since version 11 first came out.

What’s wrong with the old method?

Recall the challenge with brushing is that we want to provide users the ability to dynamically focus on one dimension value (i.e. make a selection), but we don’t want the other dimension values to vanish. Prior to version 11, this could be accomplished by using a data island (i.e. a stand-alone table that does not associate to the rest of the data model) that will provide a dimension for the users to make selections. And this dimension is necessarily decoupled from the model because it is an island.

This approach works fine, but it’s cumbersome to have to create an island table for every dimension that may be used for brushing. How can we simplify that step…..

It just got easier – Altered States!

If we think for a moment how the old method worked – that is, we decoupled the brushed dimension from the chart … which we did via the data island. Alternate states is another way to decouple data entities, and the good news is that we do NOT need to build additional tables to do it.

We will use this technique to provide brushing capability to call-out a category for new construction spending. (Note: these data are publicly available from the US Census Bureau.)

So here is the high-level algorithm we need to employ:

  1. Create an alternate state – let’s call it “A”  (without the quote symbols)
  2. Create a list box for the user to select the brushed dimension – PUT THIS LIST BOX IN STATE A
  3. Create the chart – put the chart in the default state
  4. Fine-tune the bar color in cases where the chart dimension matches the brushed dimension

Let’s break this down step by step:

Step 1 – create an alternate state

The really, really easy step … go to the Document Properties and click on Alternate States to bring up the dialog box. Add a state and call it A. (Note: I also have a state called B in the image).


Step 2 – create a list box in State A

Create a list box on the chart dimension – [Category Desc] in our case. Be sure to assign the state to A in the General tab of the list box properties.


Step 3 – create the chart

Be sure that the chart is in the inherited state; i.e. the Default state (not the A state).


The chart itself is quite simple with a single dimension: [Category Desc].


And a single expression: sum(Spend)


Step 4 – fine-tune the bar color

The bar color can be grossly controlled by the setting on the Colors tab in the chart properties. But we can also finely control the color; i.e. by using a data-driven expression by expanding the expression (on the Expressions tab of properties) and manipulating the Background Color.

The expression we need to use here will compare the chart’s dimension [Category Desc] (which is in the Default state) to the value the user selects for [Category Desc] in the A state. If the two value match, then color that bar differently than the rest — this is the “brushing.”

if(match([Category Desc], only({A} [Category Desc])), $(vData2Color))

where vData2Color is the rgb() definition for an emphasis color (pull out your copy of Few’s “Information Dashboard Design” if you need inspiration.)

Notice that invoking the alternate state is as simple as putting the state’s name A in the set definition.

The result …

The final result is a chart that in which any of the bars can be dynamically “brushed” by the users.


More of this at Masters Summit – Europe!

This is one of the topics I’ll be covering in my Visualizations course at the Master Summit in Europe in October. Rob, Oleg, Barry and I will be sharing our techniques with students from the UK and Spain – hope you can join us!


Keep on Qlikin’



Highlighting a “special” dimension in bar charts

A great way to provide your end-users added insight from a bar chart is to highlight a special dimension value.

What is a “special” dimension value?? For example, if you’re reporting on market share between your company and competitors, you should highlight “Our Company” on the chart. Stephen Few has a nice example on this topic in “Information Dashboard Design,” Chapter 6.

Another example would be if you’re a Product Manager and are looking at sales figures across various products in the line. By highlighting the specific product that you manage you can see where it stacks up against the rest … at a glance.

Imagine we really want to focus on Product = Alpha, and let’s highlight it with a saturated red bar:

So, naturally we need to figure out how to do this in QlikView, right?!

Controlling Background Color

Fortunately there is a nifty feature in the Expression Properties of the Chart object that allows you to dynamically control the Background Color of the bars. And because it itself is an expression you can use if-then logic or any other expression you can conjure.

Here I’ve chosen to use the red() function, but of course you can use any similar color function or rgb().

So this solution is just fine, except it requires that the dimension of interest be hard-coded into the expression in the chart properties. That may be okay for static analysis (like the market share example above – our company vs. our competitors). But for something that could change frequently (like Product) it would be a much more sustainable solution to be able to dynamically control the dimension of interest.

Dynamically controlling the dimension of interest

One way to make the dimension of interest dynamic is to store it in a variable. And then drop an Input Box object on the page. In this example we’ll call the variable varHighlightedProduct.

Now with a simple change to the Background Color expression –>

if(Product = $(varHighlightedProduct), red(255))

we can use an input box to control the highlighted dimension.

Another way to dynamically control the dimension of interest

The variable method works well enough, but changing the value in the input box requires a little bit of typing. Wouldn’t it be easier if there was a list box object in which the end-user could just select the dimension value of interest?? (answer = “YES”)

To build a separate list box we obviously need a data field. That can be easily accomplished with a resident load. Let’s suppose the sales data are stored in a table MySales, and the dimension field for product name is simply called Product. Then our load statement looks like:


LOAD DISTINCT Product AS [Highlighted Product] RESIDENT MySales;

This will load the unique values of Product into a table called HighlightedProduct with one field, [Highlighted Product].

Next, modify the Background Color expression so that it’s comparing against the selection in [Highlighted Product] –>

if(Product = [Highlighted Product], red(255)).

(You could get fancier with this code…allow multiple selections, restrict the list box to a single value, etc.)

So now we have a very convenient way for the end-user (the Product Manager in this example) to switch the dimension of interest using a simple list box.

I hope you find this simple example useful. Let me know if you have other ideas around this topic!