Archives

Better QlikView Trend Lines

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.
 
Styling
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.
 
The Result
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!
This entry was posted in Development, Tips & Tricks, Visualization and tagged , , . Bookmark the permalink.

5 Responses to Better QlikView Trend Lines

  1. Johan Hessler says:

    Well if you know your dimension values (AOL, MSFT and YHOO in the example above) and they aren’t to many of them you can of course use some set analysis code in the expressions instead and remove the dimension. More expressions but i still think this is a better solution if the dimension values are less than 10.

    • Thanks for the comment, Johan. You mean rather than having 2 juxtaposed charts? That’s true, you only need to do that if you have 2 dimensions. If you set up the overlapping charts correctly though, following the tips I outlined above, it really shouldn’t be a big deal.

      Regards,
      Vlad

  2. Amit says:

    Hi,

    Brilliant.

  3. Massimo says:

    Hello, how can i exclude outliers in QlikView? How can I create an algorithm for multivariate data?
    For example I have a line of linear regression with more variable and I have a few outliers, please I want known if exist a method to delete them.
    Please help me
    Thank you so much.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify via email when new comments are added