Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
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:
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.
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:
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.
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:
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.
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.
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.
And here is the scatter chart with both reference lines drawn.
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.
And here is the resulting chart and reference lines.
Source:
How To: Reference Lines and Linear Trend Lines in Your Qlik Sense Objects
You can also refer to:
How to add lineal trend in Qlik sense
Matching linest_m and linest_b function to auto-generated trendline
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:
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.
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:
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.
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:
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.
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.
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.
And here is the scatter chart with both reference lines drawn.
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.
And here is the resulting chart and reference lines.
Source:
How To: Reference Lines and Linear Trend Lines in Your Qlik Sense Objects
You can also refer to:
How to add lineal trend in Qlik sense
Matching linest_m and linest_b function to auto-generated trendline
