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).
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:
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.
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
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.
4.- In the Presentation tab, add a Reference Line based on our newly created variable.
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:
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:
7.- Add a second expression based on the following calculation:
8.- Go to the Style tab and select Stacked:
9.- Go to the Presentation tab and disable the legends:
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).
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?
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.
In order to correct this, go to the Presentation tab and disable the Pop-up Labels option.
Afterwards, add a fourth expression using this formula:
and instead of displaying it as a bar, switch it to Text as Pop-up:
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!
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!
*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) 😛
Thanks for reading and sorry for the long post. See you around!