Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simotrab
Contributor II

Linest_b, linest_m, tables and multiplications

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

axyy*beta
a

3

4(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
b45(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
c59(x)-linest_b(x,y)-LINEST_M(x,y)*(y)
d71(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.

1 Solution

Accepted Solutions

Re: Linest_b, linest_m, tables and multiplications

May be this?

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


Capture.PNG

19 Replies

Re: Linest_b, linest_m, tables and multiplications

May be this?

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


Capture.PNG

mato32188
Contributor III

Re: Linest_b, linest_m, tables and multiplications

Hi Simone,

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

BR

Martin

simotrab
Contributor II

Re: Linest_b, linest_m, tables and multiplications

Awesome as usual!! Thanks!

simotrab
Contributor II

Re: Linest_b, linest_m, tables and multiplications

If I can ask,

how could put it in a histogram?

Re: Linest_b, linest_m, tables and multiplications

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

simotrab
Contributor II

Re: Linest_b, linest_m, tables and multiplications

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
Contributor III

Re: Linest_b, linest_m, tables and multiplications

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
Contributor II

Re: Linest_b, linest_m, tables and multiplications

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.

Re: Linest_b, linest_m, tables and multiplications

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

Community Browser