How To: Reference Lines and Linear Trend Lines in Your Qlik Sense Objects

Featured Image - Qlik Sense Linear Trend Line

Why do we need trend lines and reference lines?

Here I will show you how to add calculated reference lines and sloped trend lines to your Qlik Sense visualizations.

Displaying reference lines and trend lines lend context to your charts. The gauge, for example has the great benefit of providing automatic reference for your measure. What is good? What is bad? Adding reference lines to your other visualization types provides this same benefit.

But can we do this in Qlik Sense?

As you might know, as of yet (version 3.1), we do not have the ability to check a box to add trend lines into our charts like we can in QlikView:

QlikView Trendline Options

It is simple to add trendlines in QlikView. There is no such option yet in Qlik Sense.

We do have the ability though to define reference lines in Qlik Sense using an expression:

QlikView reference line wizard.

QlikView reference line wizard.

Qlik Sense Reference Lines Area

The Qlik Sense Reference Lines Area

Luckily, with an understanding of the tools, we can define expressions to draw reference lines and trend lines to a certain extent in Qlik Sense. There is some nuance in this so I thought it would be helpful to lay out some common use-cases to illustrate. For example, some charts allow us to add reference lines explicitly. Others require us to create the reference line as a separate measure because the chart type does not allow for reference lines. Also if we are doing a linear trend line, we must use a measure to create the line because a reference line cannot slope. Can anybody say “Work Arounds”?

1. Create an Average Reference Line

Below I have created a line chart. I want a horizontal line on the Y-Axis at the average value for the chart. In the right panel, use the Add-ons > Reference lines section to add this expression:

Qlik Sense Average Reference Line Expression

To adapt this to your objects, simply replace the expression inside the aggr function with the main expression for your object. Then replace the dimension with the primary dimension in your chart.

Qlik Sense Reference Line Visualization

By the way, this method can be used in really any kind of visualization that includes reference lines. It is the tried-and-true method for adding the average level into your charts. This can be used in QlikView as well for charts that do not include the trend lines feature, like straight tables.

2. Create a Linear Trend Line

I always took for granted QlikView’s simple ability to check the box and create a sloped linear trend line for any of my visual charts. It is such a no-brainer that I am positive this feature will exist in Qlik Sense at some point. But for now, we can create a sloped line. We just have to do the math ourselves.

To create a bar chart with a sloped trend line, I have to actually use a combo chart (below). This is because the normal Reference lines area does not support the drawing of a line that is sloped (not parallel to the x-axis). So instead of using the Reference lines area, we simply create an additional measure that visualizes as a line in Data > Measures. The expression is somewhat more complex:

Qlik Sense Sloped Trend Line Measure

This looks pretty complex, but it is taken from our 8th grade algebra: y=mx+b where m is the slope and b is the y-intercept. To adapt to your charts, replace the base expression sum(ExtendedAmount) with your base expression from the first measure. And then use your data dimension in the chart to replace MonthYearID above. I found this method on the Qlik Community.

Qlik Sense Sloped Trend Line Visualization

3. Create a Reference Line in the Middle of the Axis

Let’s look at a quadrant style scatter plot. Typically in this type of visualization, there is analysis benefits in grouping the chart into 4 quadrants to segment the data. Take, for example, the Gartner BI “Magic Quadrant” that gets published each year.

2016 gartner magic quadrant for bi and-analytics platforms

In this example, we can use the Add-ons > Reference lines area to create an expression that finds (approximately) the halfway point for the axis. By changing the base expression and dimension to match the axis you are developing the line for, you can replicate this for both the horizontal (x-axis) and vertical axis (y-axis). We are basically finding the highest value in the chart, adding a little buffer to account for the chart axis maximum and then cutting it in half to find the midway point. This expression will work equally well in QlikView Presentation > Reference Lines.

Qlik Sense Midway Reference Line Expression

And here is the scatter chart with both reference lines drawn.

Qlik Sense Halfway Reference Line Scatter

I did attempt to create a sloped line that would typically run through a correlated scatter plot, but was unable to do so. Firstly, only reference lines perpendicular to the axis are allowed. You cannot do this by adding a measure either because the scatter object only allows dots as the visualization.

4. Create a Median Reference Line

In this last example, we will use the same scatter plot to draw our reference lines at the median of each axis, rather than the visual halfway point. The median is the area of the chart where half of the values are above the line and half of the values fall below the line. The same process as above will apply, but the expression will be a little different.

Qlik Sense Median Reference Line Expression

And here is the resulting chart and reference lines.

Qlik Sense Median Reference Lines Visualization

Final Thoughts

So even though we might not have an easy way to create trend lines and reference lines, we still have the ability to get to where we want to go if we do a little extra work.

I did attempt to get to an expression that would give me a curved line (polynomial to 2nd degree for example), but the math to do that ended up a little beyond my pay grade.

I welcome your thoughts and comments. Are there other commonly used reference line expressions in your tool bag? I would like to know!

Happy Qliking.

 

6 comments… add one
  • Reply Steve Dark December 20, 2016, 3:17 pm

    Hi Aaron,

    Great post, Thanks for sharing.

    There is functionality coming soon in both QlikView and Qlik Sense that will allow the sending of data off to R or Python to calculate a line of best fit our a future projection. This will give so many more options to people.

    I still miss the check box that was in QlikView though.

    Steve

  • Reply Lech Miszkiewicz December 21, 2016, 5:17 am

    This is very well described and i went through it before when one of my clients asked me to create a trend line in QlikSense.
    I was aware of reference lines on certain charts. There was however the biggest challenge i could not get working which is a line on scatter chart where x=y. That was possible in QlikView but i could not replicate it in Sense.
    Hopefuly we will get this functionality one day.

    Thanks and best regards

    Lech

  • Reply Aaron Couron December 21, 2016, 4:52 pm

    Lech,
    You are correct about x=y in a scatter. Currently no way to get that done because it represents a sloped line. Judging from Steve’s comment above, we should be able to get to that point fairly shortly. Thanks for the comment.

  • Reply Martin Mahler December 24, 2016, 2:25 am

    Polynomial 2nd degree is beyond your pay grade? 9th graders know that stuff:)!

  • Reply Cotiso Hanganu February 10, 2017, 7:49 am

    Also missing the diagonal lines in scatter. And hope Steve is right !

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons