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 aggregations 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.
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')
