2 Replies Latest reply: Jun 27, 2018 6:07 AM by omar bensalem RSS

    How to calculate Linear Trend

    P Kumar

      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

        • Re: How to calculate Linear Trend
          Chennaiah Nallani

          Share sample data

          • Re: How to calculate Linear Trend
            omar bensalem

            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




            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