Audio

Threshold Bar Chart + Christmas Card

One of the biggest problems about being a QlikView Developer is that every time you see an interesting visualization, you immediately start wondering how you can use it in your own dashboards. For instance, a couple of days ago, I was wandering around in Twitter when I saw a message which, apparently, had something to do with the quality of the air in Berlin (sorry, but my German is not very good).

TBC_Twitter

As you can see, the structure of this chart is not overly complex: classic bar chart using a time dimension and a standard expression. However, a simple visual cue composed by a reference line and a different color makes it easier to identify where (of in this case, when) an element surpasses the threshold. In my opinion, it is an elegant and useful way to present data.

Since I didn’t have access to this particular data set, I decided to work with a sales QVD from one of the projects I’m currently working on and, after spending a few minutes in QlikView, this was the result:

TBC_1

I don’t know if this kind of representation has an official name, but I called it “Threshold Bar Chart” <<Patent pending>>. I know… I’m awful at naming things so, if you have a better title, be sure to share it in the comments section! As usual, you can download the QVW with the final chart here. The recipe is simple:

1.- Create a bar chart using Week as dimension and sum(Value) as the expression.

TBC_2

2.- Remove everything but the bars by disabling the following options:

  • General > Show Title in Chart
  • Dimensions > Show Legend
  • Axes > Hide Axis
  • Layout > Use Borders
  • Caption > Show Caption

TBC_3

3.- Create a variable called Threshold either in the script or in the Variable Overview Panel. Using a variable instead of a hardcoded number allows the user to easily change the limit depending on the context of the data.

TBC_4

4.- In the Presentation tab, add a Reference Line based on our newly created variable.

TBC_5

5.- Now, here’s the interesting part. Making the bars blue or red depending on whether they’re over or under the threshold is relatively easy:

TBC_6

However, we only want to highlight the part of the bar that surpasses the reference line. That means that one expression should be depicted in two colors at the same time, right? Sadly, that’s not technically feasible. Or is it?

Let’s tackle this from a different angle. What if we divided the bar into two expressions, one containing the area below the line (blue) and the other representing the upper section (red)? After that, we could simply stack them in order to give the impression of a single object.

6.- Change the definition of the first expression to this formula:

TBC_7

7.- Add a second expression based on the following calculation:

TBC_8

8.- Go to the Style tab and select Stacked:

TBC_9

9.- Go to the Presentation tab and disable the legends:

TBC_10

10.- In order to create the reference labels in the lower part that point out where the new year starts, I added a third expression using the Text on Axis representation (You might not need this part, it’s up to you).

TBC_11

11.- Make the final adjustments to the colors and axes. Presto, our visualization is ready! Additionally, you can create an Input Box so the user can change the value of the variable. If you’re willing to spend two more minutes on this exercise, why not also add some buttons to make these movements easier?

TBC_1


Update: Steve’s keen eye found an issue with the popups in this chart. If you hover the cursor over any element, you’ll only get half of the story (either the red or blue value), but never the complete number.

TBC_12

In order to correct this, go to the Presentation tab and disable the Pop-up Labels option.

TBC_13

Afterwards, add a fourth expression using this formula:

TBC_14

and instead of displaying it as a bar, switch it to Text as Pop-up:

TBC_15

With this trick, the popup will be the same regardless of the part of the bar you’re touching. Besides, you can create better labels by adding extra KPIs!

TBC_16


By the way, I think this will be my last post for 2015, so I wanted to thank you all for taking the time to read my blog. I hope you enjoy the holidays with your family and friends. My best wishes for 2016!

Christmas

*Yes, the tree is based on a Combo Chart and the star is built with a Radar. Also, I guess I finally put a 3D pie chart to good use. To download the Christmas card, click here.


Finally, don’t forget that Packt Publishing goes crazy every December and gives away all their books and videos for $5 USD! It’s a great opportunity to get your hands on some awesome titles for just a few bucks. My recommendation, of course, is Creating Stunning Dashboards with QlikView (QlikView book of the month and Packt Top 20 for 2015) 😛

Promo Packt

However, it’s also your chance to check out the new QlikView books, sharpen your abilities in JavaScript (D3.js or Highcharts) to build amazing extensions or even go beyond and explore R or Python. If you can’t decide, let me recommend you one of my favorite titles, Data Visualization: a successful design process by Andy Kirk. I guarantee you won’t be disappointed.

Thanks for reading and sorry for the long post. See you around!

Advertisements

7 thoughts on “Threshold Bar Chart + Christmas Card

  1. Hi Julian,

    Like the threshold bar chart. The problem I have found doing this kind of thing in the past is that the ‘tool tip’ popup value is very misleading – as it only gives you the portion above or below the line – rather than the whole value. You can (and should) of course turn off the incorrect hover text, but it is difficult to get the actual value on a hover – it may be possible though.

    Also, I like the Christmas tree. I came very close to doing a similar looking tree, but opted to do something in Sense – and thought getting the tree to look just right was unlikely.

    Steve

    • Hi Steve,

      Thanks a lot for the heads-up, I totally forgot about that! I’ve updated the tutorial with two new steps to correct this issue 🙂

      Have a great year!!!

      Julian

  2. Hi Julian, great little trick with the bar chart. Visually very creative and just works. Thanks for sharing and will be sure to try this out in a future app.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s