Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- How to calculate Linear Trend

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

pawwy1415

Contributor II

06-27-2018
04:32 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi Experts,

Can any one please help me to calculate the linear trend line like below and please explain what is linear trend.

I have 4 measures like below

Sec Assess Total calculated as Sum([SEC A])

Sec Liabilities Total calculated as Sum([SEC L])

**Der ****Assets Total** calculated as Sum([DER A])

Der Liabilities Total calculated as Sum([DER L])

Please help me to calculate the **linear for ** **Der ****Assets Total **

**Thanks in advance**

Solved! Go to Solution.

392 Views

1 Solution

Accepted Solutions

omarbensalem

Esteemed Contributor

06-27-2018
06:05 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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:

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

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”?

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.

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.

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.

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

https://github.com/tmargolis/SSE-LinearRegression

3 Views

2 Replies

ChennaiahNallani

Contributor III

06-27-2018
05:21 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Share sample data

3 Views

omarbensalem

Esteemed Contributor

06-27-2018
06:05 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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:

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

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”?

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.

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.

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.

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

https://github.com/tmargolis/SSE-LinearRegression

4 Views