Linest_b, linest_m, tables and multiplications

[simotrab]

Hi community,

a very fast question. I'm using the nice Linest_b function.

https://help.qlik.com/en-US/sense-cloud/Subsystems/CloudHub/Content/Scripting/StatisticalAggregation...

My dataset is something like:

load*inline

[a,x,y

a,3,4

b,4,5

c,5,9

d,7,1

];

So I need to have a table with this simple result

a	x	y	y*beta
a	3	4	(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
b	4	5	(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
c	5	9	(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
d	7	1	(x)-linest_b(x,y)-LINEST_M(x,y)*(y)

But I cannot manage to have a result, everything goes grey. I've tried also to do something like

(x)-num(linest_b(x,y))-num(LINEST_M(x,y))*(y)

or

sum(x)-num(linest_b(x,y))-num(LINEST_M(x,y))*sum(y)

But nothing happened but to have an useless total.

Could I ask you an help?

Thanks in advance

EDIT:

Due further developements, it is, I'm using the model with y,x , not x,y. The reasoning are the same.

[sunny_talwar · Accepted solution]

May be this?

(x - LINEST_B(TOTAL y, x) - LINEST_M(TOTAL y, x)) * y

[mato32188]

Hi Simone,

have you tried something like LINEST_B(TOTAL x,y)?

BR

Martin

[simotrab]

Awesome as usual!! Thanks!

[simotrab]

If I can ask,

how could put it in a histogram?

[sunny_talwar]

First of all, do you agree with the numbers? I not not even sure if you get the right numerical output or not

[simotrab]

I've double checked the formulae (qlik sense, theory and R) and it is better in this way

y-(linest_b( total y,x))-(LINEST_M(total y,x))*x

However the issue was the TOTAL, whom saves my day.

[mato32188]

Hi Simone,

use bar chart object, insert add Dimension: x-(linest_b( total x,y))-(LINEST_M(total x,y))*y  as Dimension, and put Count(a) as Expression.

BR

Martin

[simotrab]

Hi Martin,

thanks. I'd like to use the Qlik Sense Histogram, not a barchart. It needs only one "field", nor a dimension, neither a measure specifically. Using a barchart works technically, bit it is going to count "one" for each value of the dimension, making it not useable. Generally a Histogram should have only a measure and you can vary the width of the bins.

[sunny_talwar]

May be create a new field in the script and use that to create your histogram

Table:

LOAD * INLINE [

    a, x, y

    a, 3, 4

    b, 4, 5

    c, 5, 9

    d, 7, 1

];

Left Join (Table)

LOAD LINEST_B(x,y) as b,

LINEST_M(x,y) as m

Resident Table;

FinalTable:

LOAD *,

x-b-m*y as z

Resident Table;

DROP Table Table;

Now use z to create your histogram