Linked bar charts

By Wesley Smit

Two questions from business users that will probably sound familiar to most QlikView Developers:

“We currently use this report. Can you re-make this in QlikView?”

and

“Our graphics designer came up with this dashboard. Can you do that in QlikView?”

Quite often you’ll find the answer to be yes. Although admittedly, you often may also want to suggest another approach, especially when asked the first question.

Recently I was asked if the following chart could be built in QlikView:

Linked Bar Chart - The Challenge

As you can see, this chart compares relative-to-total amounts for two periods. There is a vague area that connects both bars and kind-of-but-not-so-much shows the change between the periods. I had to think a bit harder before responding to that question…

At first I thought, maybe I can just create a line that attaches both bars and create it that way. Well, that didn’t work. But, fooling around in chart properties of both the bar- and line charts, I found a way to do it. I wouldn’t recommend this chart for it is rather hard to make, not that fancy and really doesn’t say much…

…But as the client explained, the goal of this dashboard was to impress rather than inform. And hey, the customer’s always right? Right?

So, here’s how it’s done.
Since it’s impossible to do this in a single chart, you’ll have to put two charts on top of each other, and make the top one 100 % transparent in the colors tab.

First, you create a stacked, horizontal bar chart. In this example, I have two dimensions: Year and Brand, in that order. I used the following expression:

Expression segment vs total

The  TOTAL qualifier is used in combination with the Year field to have the percentages per year in every segment of the bar. Also, the set-analysis is to make sure always exactly two years are shown. Fortunately, that was the requirement as this trick doesn’t work with more than 2 years. (or rather, it becomes a lot more complex)

To create the values in the chart, in the expressions tab, click +sign , select ‘text color’ and enter ‘white()’ as the expression. Also in the expressions tab select ‘Values on data points’. In the presentation tab, create an open area between the bars by setting the cluster distance. Select ‘Plot values inside segments’ to show the values inside the bar. In the Axis tab, hide only the X-axis. In the number tab, select fixed to <2> decimals and ‘show as %’. Furthermore, get rid of all captions, titles and other stuff that isn’t used. You should now have something like the desired image, except of course for the middle, vague section that connects the two bars.

Now, let the ‘magic’ begin.
Copy the current chart for you want to use the same dimensions, expression etc..

Go to the chart properties. In the general tab, select line chart. On the dimension tab, deselect ‘Show legend’ for the Year dimension, you don’t want to see two legends for your years. In the expressions tab, make sure the expression is shown as a line, without values on data points. In the style tab, select the fourth option, area chart. In the colors tab, select the colors you used one by one and then, in the ‘color area’, change the transparency. Finally, in the layout tab, make sure your border width is 0 points.

Now the last bit is to size them properly and place them exactly on top of each other. Besides the mouse, you can use the keyboard or the settings on the caption tab. Et voila!

Linked Bar Charts in QlikView

You can download an example application through the link below.

Download the linked bar chart example application

About The Author

Wesley Smit

Hi there, I'm Wesley - Business Intelligence Consultant at Bitmetric since the beginning of 2014. With my non-tech background in Business Economics and Organization Sciences, I'm now developing technical skills and knowledge of data visualization, especially within QlikView. Via this blog, I will share interesting features that I come across. You can follow me on Twitter at @smitwuith1990.

25 Comments

  • 1
    April 25, 2014 - 15:44 | Permalink

    This is really awesome! I saw it on twitter a few days ago and already used it into a dashboard at a client – it was a hit.

    Thanks for sharing

  • 3
    Anderson Oberdan
    April 25, 2014 - 18:15 | Permalink

    Hello, I am Brazilian and working with QlikView. Incredible thought his example. Congratulations and thanks for sharing.

  • 5
    Jeroen Jordaan
    April 28, 2014 - 15:26 | Permalink

    Hi Wesley,

    Very nice presentation if you want visualize the change between years and a part to the whole.

    I will definitely be using it in futher dashboards.

  • 6
    Ignacio
    May 15, 2014 - 14:58 | Permalink

    Hi Wesley this is excellent! Very clever use of QV charts!

    Thanks for sharing!

  • 7
    July 13, 2014 - 20:12 | Permalink

    hello Hesley,
    thanks for the post, I am very interested.
    You can republish the sample file because Dropbox does not allow downloading it?
    Thank you
    Paul

  • 8
    Ankur
    July 24, 2014 - 13:07 | Permalink

    Hi Wesley,

    Great work done. This is what we call “Creativity”.

    Just a suggestion, add “Brand=” like below in all denominator expression:

    sum( TOTAL {$}(Value))

    This will prevent converting a single band value to 100% whenever user select any single band from selection box. This expression will bypass the band selection from denominator and show real value of band when selected.

    Thanks
    Ankur

    • 9
      Ankur
      July 24, 2014 - 13:08 | Permalink

      “sum( TOTAL {$}(Value))”

      • 10
        Ankur
        July 24, 2014 - 13:11 | Permalink

        I am trying to add compelete expression but whatever in the set analysis in not coming up in my comment. You just need to add “Brand=” in your set expression to make this work.

    • 11
      July 24, 2014 - 13:18 | Permalink

      Hi Ankur,

      Thanks, I’m glad you like it.

      Your suggestion is certainly valid, if you always want to show all brands.
      In this example, however, there’s also the possibility to select brands, and than show parts to the whole.

      Kind regards,

      Wesley

      • 12
        Ankur
        July 24, 2014 - 14:04 | Permalink

        Hi Wesley,

        Thanks for your reply.

        My suggestion is valid only if someone select brand name from selection box. If you are displaying the data for all brands then there is no need to add any thing in set expression. Currently the value for A brand is 8.64% for 2013 and when somebody select A from list box this value get converted into 100%. To prevent this converting to 100% you need to put “Brand=” into your set expression.so that it stick on 8.64%.

        Regards
        Ankur

  • 13
    Josh
    August 16, 2014 - 01:23 | Permalink

    Nice! concise explanation.

  • 14
    Yas
    November 4, 2014 - 13:28 | Permalink

    The file is not there… could you please upload the file or send it to my email?

    Many thanks
    Yas

  • 15
    Frank
    February 2, 2015 - 12:08 | Permalink

    Hi Wesley,

    Looks pretty good! I want to give it a try, but it’s not possible to download due to some download issues. To be more specific, your dropbox account is temporarely unavailable 🙂
    Is it possible that you send the qvw to my email address? Thanks in advance!

    Regards,

    Frank

  • 16
    Steve Knight
    March 18, 2015 - 11:37 | Permalink

    Hi, how can I show only lines that connect the bars without the filling colors

    Thanks
    Regards

  • 17
    Deepak Prasad
    May 5, 2015 - 10:31 | Permalink

    Link is broken.. Can anyone send me the qvw?

  • 18
    Fabio
    May 14, 2015 - 19:53 | Permalink

    It’s fantastic!!!

    Are there another link to download the exemple?

    Regards,

  • 19
    Satish
    June 2, 2015 - 12:44 | Permalink

    Hi Wesley,

    Link broken any other way to download the sample. Thanks

  • 20
    Michael
    November 8, 2015 - 22:23 | Permalink

    Hi the link is broken, can you please re-upload?

  • 21
    DeRe
    November 14, 2015 - 03:07 | Permalink

    This is a fantastic chart! I have tried to replicate it following the instructions, but I can’t figure out what I need to change. Can you send the example file? The link just gives a message about too much traffic, which can only be because the chart is so awesome! Please, please, please? Thank you, thank you, thank you.

    • 22
      November 17, 2015 - 12:33 | Permalink

      Hi Dena,

      I have changed the location, it should work now. Happy Qlikkin’!

  • 23
    Richard Ouellette
    November 16, 2015 - 12:16 | Permalink

    I also have not been able to download the example qvw. Can you fix and/or assist?

    • 24
      November 17, 2015 - 12:32 | Permalink

      Hi Richard,

      I have changed the location, it should work now. Happy Qlikkin’!

  • 25
    Sajad
    March 13, 2016 - 17:51 | Permalink

    Wow, absolutely fantastic chart…..Thumps up for you Wesley.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Introducing a new author at QlikFix: Wesley Smit

    One of the reasons I started this blog in 2010 was not only to share what I know about QlikView,...

    Close