Custom Regression line

TARGIT comes with a built-in Regression line option:

See also: https://community.targit.com/hc/en-us/articles/4402367689873-Regression-line

However, this built-in Regression line option only works on original measures from the data warehouse. If you add client based calculations to your object, the built-in Regression line will not work on those. Hence, you may want to calculate your own, custom Regression lines.

To do this, you will need to work with a total of three additional calculations to get to the desired (straight) regression line.

Custom Regression line Example

In the following example, I am looking at a crosstab with two original measures: Revenue and Units Sold. From these I have calculated my 'c1 Unit price' as sum(d1, 0, m1) / sum(d1, 0, m2). The calculation is added as a calculated column and can be referred to as 'c1' in subsequent calculations.

Now I want to calculate regression values for my 'c1 Unit price' calculation - resulting in the regression line in the bar chart below.

To achieve the values for the custom regression, I will need to work with two intermediate calculated columns, 'c2 XX' and 'c3 XY' before getting to the final 'c4 Regression' calculation.

The formulas you need, are these:

• For 'c2 XX' use:
  • XX:((count(d1, d1:0, 0) - 1) * (count(d1, d1:0, 0) - 1))
• For 'c3 XY' use:
  • XY:((count(d1, d1:0, 0) - 1) * sum(c1, 0, 0))
• For 'c4 Regression' use: 
  • ((AvgY:(avg(c1, all, 0)) * AvgXX:(avg(c2, all, 0)) - (AvgX:((count(d1, all, 0) - 1) / 2) * AvgXY:(avg(c3, all, 0)))) / (AvgXX - AvgX * AvgX) + (count(d1, d1:0, 0) - 1) * (AvgXY - AvgX * AvgY) / (AvgXX - AvgX * AvgX))