Highlighting a “special” dimension in bar charts

November 10th, 2010 by Bill Lay Leave a reply »

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:

HighlightedProduct:

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!

Advertisement

9 comments

  1. Barry says:

    Bill,

    Excellent tip! I definitely will be using this in future projects.

    Kind regards,
    Barry

  2. Eran says:

    Excellent tip!
    Thanks..

  3. Bill,

    Good post; it’s an area I’ve explored a bit further here: http://qvdesign.wordpress.com/2012/02/24/different-ways-to-interact-display-information-with-bar-charts-tables/

    Allowing full dynamic highlighting of Bar Charts and Straight Tables.

    From what I can see this ‘Highlight When Selected’ is a key feature of Tableau; good to know Qlikview can do it as well!

    All the best,

    Matt

  4. Abhijit says:

    Nice Post Bill.
    I have one query related to this topic.

    Is there any way to apply diffrent colors on one bar.

    eg: a bar showing % utlisation. if utilisation 70% and <=90% then Amber() else Red().

    Additionaly, this is not a stacked bar , so colors based of value of dimesion will not work here.

    please suggest any idea.

    • Bill Lay says:

      I think you could possibly get what you want by adding a second expression to stack. (Contrast this to stacking dimensional slices. Even though these are not dimensions you are stacking you still turn on the Stacked option in the Style menu.)
      And then simply color the two expressions differently. The point is that the sum of the first and second expressions add up to the total of the metric. Bill

  5. zeeshan says:

    if i require current month to be color ? what should i do?

    • admin says:

      Hi – I would probably create a flag in my Calendar table ([Is Current Month ?]) and then use a conditional in the Background Color for the Expression. The expression would be something like:
      if([Is Current Month ?], lightRed()) // using lightRed() or whatever color you choose

      Obviously you need to use Month as your chart dimension; or some field that equates to month.

Leave a Reply