Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Calculates the linear regression of a set and returns the coefficient of determination, R2.
Linear regression, that uses the least-squares method, calculates the equation of a regression line (that is, the best-fit line for a series of points). The regression line has the following equation, where a is the slope and b is the intercept:
y = ax+b
The LinRegR2 function evaluates the specified setagainst the first numeric expressionto obtain the values for the y-axis. The function then evaluates the specified set against the second numeric expression, if specified, to obtain the values for the x-axis. If the second numeric expressionis not specified, the function uses the current context of the cells in the specified set as the values for the x-axis. Not specifying the x-axisargument is frequently used with the Time dimension.
After obtaining the set of points, the LinRegR2 function returns the statistical R2 that describes the fit of the linear equation to the points.
The LinRegR2 function ignores empty cells or cells that contain text or logical values. However, the function includes cells with values of zero.
The following example returns the statistical R2 that describes the goodness of fit of the linear regression equation to the points for the unit sales and the store sales measures.
LinRegR2(LastPeriods(10), [Measures].[Unit Sales],[Measures].[Store Sales])