Linear regression in table charts

Linear regression trend lines can be shown in QlikView bitmap charts by means of the Trendlines option in the Expressions page of Chart Properties. It is also possible to display the regression equation.

Example:  

If you want to display the regression data in e.g. a table chart, the regression must be calculated. The LINEST_M and LINEST_B aggregation functions will give you the required slope and y-intercept values of the linear regression. The LINEST_M and LINEST_B aggregation functions always correspond to a continuous x-axis, which means that you have to make this setting on the Axes tab of the chart properties.

To calculate correctly, these functions need to have the entire chart aggregation (expression iterated over dimension) as input. This can be achieved by defining an advanced aggregation function containing the same base expression and dimensions as the containing chart. The advanced aggregation function is then used as parameters to the LINEST aggregations. The resulting expression could look like follows:

linest_M(total aggr(TransVal,TransID),TransID)*TransID + linest_b(total aggr(TransVal,TransID),TransID)

The Only function is implied around all occurrences of TransVal and TransID. The LINEST agregations should be made with the TOTAL qualifier, else would the regression parameters be calculated per data point rather than for the whole set of data. The result can be seen in the combo chart below where the regression is shown as a regular line expression.

Note that the trend line here is not a traditional QlikView trend line, but a regular expression plotted as line. You can see the difference from the fact that the expression plot, as opposed to a traditional trend line, is not extrapolated outside the first and last data points.

Note: The calculated values for linear regressions correspond to those displayed in a graph using a continuous axis (recommended). The calculated values may differ from those displayed in a graph using a discrete axis (not recommended). A continuous axis represent calculated values; a discrete axis represent displayed values. This means that NULL values are not included in a discrete axis.

This chart can converted to a straight table:

The following expressions are used, in order of appearance:

Round(Sum(TransVal),'0.1')

Round(LINEST_M(TransVal,TransID,TransID),'0,1')

Round(LINEST_B(TransVal,TransID,TransID),'0,1')

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?