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:

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:

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  , 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!

You can download an example application through the link below.

Download the linked bar chart example application

About The Author

Bitmetric Admin

Blog posts from the Bitmetric team. At Bitmetric, Qlik is what we do. We've been doing it for years, and we can do it for you! ;)