Build your own completely customizable trend lines in QlikView!
The pros and cons of trend line use in business intelligence are debatable. However, what is not debatable is that QlikView's native trend-lining capabilities are sadly lacking. I can't speak for other developers but, for me at least, it has always been a source of embarrassment to have to explain to clients that QlikView lacks the ability to match trend line colors to main line colors, or even set basic trend line properties such as width or dashing. For instance, these are quite literally the best linear trend lines that you can create with QlikView's native feature set:
There are two main problems with the above trend lines: (1) the colors do not match the main line colors (imagine if the above line chart plotted 10 different stocks instead of 3—it would be an indecipherable mess!), and (2) the trend lines are too bold and call too much attention to themselves; while I do want to see the trend, I do not want to detract from the main data displayed by the chart.
Math to the Rescue!
Fortunately, our old friend from school, Algebra, can save the day. Rather than relying on QlikView's native trend lining capabilities, we can simply create our own trend lines by using the formula y = mx + b, where m is the slope of the line and b is the Y-axis intercept. The x component of the equation can simply be represented by the QlikView rowno() function, which will return 1 for the first dimension value, 2 for the second, etc. A detailed explanation of how to calculate m and b can be found here.
QlikView does not gracefully support line charts with two dimensions and more than a single expression. In order to overcome this limitation, we can create a separate line chart for our trend lines and use chart layering to juxtapose one chart over the other. Some tips to keep in mind to make this procedure as smooth as possible:
- Begin by cloning your original chart; this will allow you to keep formatting and component positioning consistent.
- The top-layered chart should be your original line chart (the one without trend line values) in order to preserve mouse-over pop-up text capabilities. The frame background of this top chart should be set to transparent.
- To preserve positioning between the two charts, the bottom chart should simply make text transparent rather than hiding it altogether. This applies to (a) the chart title on the General tab, (b) the font on the Font tab, and (c) the axis fonts on the Axes tab. The only component that truly needs to be hidden in the bottom line chart is the legend. You may also choose to make an external legend using an adjacent/overlaid straight table and disable the legend on the original chart, as well, in order to keep chart components identical between the two charts.
- Set both charts' axis minimums and maximums to the same static values to ensure a correct overlap.
- Note: the entire solution described in this article will only work if the line charts do not have horizontal scrollbars on the X-axis. Because the solution uses two layered charts, they cannot scroll together.
It is my sincere hope that our good friends at Qlik will see fit to enhance QlikView's own native trend lining capabilities one day—certainly not an unreasonable expectation from a serious BI solution that is firmly entrenched in Q1 of Gartner's Magic Quadrant. Until that day comes, however, the solution outlined above can produce results that are quite elegant and can provide not only dramatically improved formatting, but even direct user control of trend line appearance and visibility:
Please feel free to download the app using the link below and see the difference for yourself. As always, happy Qliking!