linear trend

[lizardjuice]

I need to implement a linear trend function like the one in Excel that takes a known set of y-points and a known set of x-points and returns a linear trend of x-points.

Does anyone know how to implement this in ActuateBasic?

 

[nagornyi]

For a set of pairs (X,Y) linear trend is defined by equation
Y_trend=b0+b1*X
The coefficients b0 and b1 are calculated by the following formulae:

b1=Sum((X-A(X))(Y-A(Y)))/Sum((X-A(X))^2)
b0=A(Y)-b1*A(X)

Here A(X) and A(Y) are average values of X and Y sets.
If X,Y pairs are in the table tbl, like tbl.X and tbl.Y, the coefficients b1 and b0 can be obtained right in the Actuate SQL by creating 2 computed columns with the following formulae:

for b1:

select sum((X-(select sum(X)/count(X) from tbl))*(Y-(select sum(Y)/count(Y) from tbl)))/sum((X-(select sum(X)/count(X) from tbl))^2) from tbl

for b0:

select (sum(Y)/count(Y) - sum(X)/count(X)*sum((X-(select sum(X)/count(X) from tbl))*(Y-(select sum(Y)/count(Y) from tbl)))/sum((X-(select sum(X)/count(X) from tbl))^2)) from tbl

Now in the detail section you can calculate the trend value by creating floating point control with the following ValueExp:
[b0]+[b1]*[tbl.X]