topic Re: Linest functions in QlikView

Linest functions

linoyel — Wed, 25 Nov 2020 16:16:04 GMT

Hi, does anybody know how to retrieve an "a" value in formula y=bx+a from LINEST functions?

Re: Linest functions

swuehl — Thu, 20 Apr 2017 11:56:09 GMT

You can use the linest_b() function for that

LINEST_B - chart function ‒ QlikView

Re: Linest functions

linoyel — Thu, 20 Apr 2017 11:59:22 GMT

Linest_B doesn't give an "a" value from the formula y=bx+a

Re: Linest functions

linoyel — Thu, 20 Apr 2017 12:00:54 GMT

By the way, I'm talking about manual accumulative calculation. Maybe in accumulative it's different?
It seems that "a" = Linest_M+Linest_B. Am I right?

Re: Linest functions

swuehl — Thu, 20 Apr 2017 12:01:31 GMT

Not sure why not. From the HELP:

LINEST_B() returns the aggregated b value (y-intercept) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in the expressions given by the expressions x_value and y_value, iterated over the chart dimensions.

Re: Linest functions

swuehl — Thu, 20 Apr 2017 12:33:01 GMT

Linoy, the linest functions consume a table with two related columns, one showing 'x' values, the other 'y' values (if you think of a cartesian x/y graph). These functions then calculate the 'slope' resp. the 'y-interception' of a linear regression.

Linear regression - Wikipedia

If the y-values in that source tables are cumulative numbers or not doesn't really matter for the linest function, that's part of your task as a developer to create / calculate the correct numbers to feed into the linest functions.

(There is no option or something like that you can enable with the linest functions to enable an automatic accumulation, if that's what you were thinking of, like in the line graph).

Re: Linest functions

linoyel — Thu, 20 Apr 2017 13:13:22 GMT

Yeah, i saw that but can you please look at the sample example attached? The manual calculated regression line matches the automatic one but b from y=mx+b seems to be equal to Linest_M+Linest_B:

3 + (- 0.67) = 2.3333

Re: Linest functions

swuehl — Thu, 20 Apr 2017 13:19:51 GMT

Yeah, I believe the automatic linear regression equation shown in the chart is crap.

Re: Linest functions

linoyel — Thu, 20 Apr 2017 13:21:48 GMT

Ha ha ha

So, "b" value from the y=mx+b equation is b=Linest_M+Linest_B?

OR:

b = Linest_B and b value from automatic equation is wrong?

Re: Linest functions

swuehl — Thu, 20 Apr 2017 13:27:53 GMT

The b value from the automatic equation is wrong, it's giving y-intersection value at x=1 (first x dimensional value), not at x=0. The equation clearly doesn't match the shown line.

Re: Linest functions

linoyel — Thu, 20 Apr 2017 13:30:55 GMT

Thank you for the clarification, Stefan! You've helped me a lot!

Re: Linest functions

swuehl — Thu, 20 Apr 2017 13:33:45 GMT

You can see it also when you change the x-values:

LOAD * INLINE [

Name, ValueID

1, 2

6, 6

7, 8

];

--> automatic equation doesn't change! Only y-values have an impact on equation values

I assume Qlik is just using the dimensional values autonumber'ed 0...n and use this as x-axis values.

Advantage: you can calculate an regression line also on textual x-values

Disadvantage: it's not a correct regression calculation, IMO

edit: Forgot to say: the automatic equation is correct if you change axis to continouus axis on axis tab.

Re: Linest functions

linoyel — Thu, 20 Apr 2017 14:38:50 GMT

Thanks again, Stefan. Now i understand better this issue
Great edit at least the equation is right now!

Re: Linest functions

yuankai0102 — Wed, 15 Aug 2018 06:50:32 GMT

Hi, i understand that linest function works well for univariate linear regression. what about mutlivariate regression?