19 Replies Latest reply: Dec 27, 2017 10:10 AM by Sunny Talwar

# 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/StatisticalAggregationFunctions/statistical-aggregation-functions.htm

My dataset is something like:

[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?

EDIT:

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

• ###### Re: Linest_b, linest_m, tables and multiplications

May be this?

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

• ###### Re: Linest_b, linest_m, tables and multiplications

Awesome as usual!! Thanks!

• ###### Re: Linest_b, linest_m, tables and multiplications

Hi Simone,

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

BR

Martin

• ###### Re: Linest_b, linest_m, tables and multiplications

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

• ###### 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.

• ###### 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

• ###### 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:

a, x, y

a, 3, 4

b, 4, 5

c, 5, 9

d, 7, 1

];

Left Join (Table)

LINEST_M(x,y) as m

Resident Table;

FinalTable:

x-b-m*y as z

Resident Table;

DROP Table Table;

Now use z to create your histogram

• ###### Re: Linest_b, linest_m, tables and multiplications

Thanks Sunny (sorry for the delay),

but I need to have the solution alive, that is not possible to do it working on the load script. Those data are more simple than the real data, furthermore I'd like to have different regression at the variation of the selections.

There are some nice guide to bend a barplot to a histogram, but the problem is that I'd like to use the "new"features, being I hope more simple.

If they are not easy for their classical use (you know, histograms are used for the study of the residuals in a regression), probably I'm doing something wrong.

• ###### Re: Linest_b, linest_m, tables and multiplications

You want regression to change based on selection and yet you want it in the script? I am not sure how this would work.... May be someone else might have some good ideas for you

• ###### Re: Linest_b, linest_m, tables and multiplications

No, sorry, I was not clear: only in selections. Everything is ok till now with your help, the only issue is to put the results of this

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

in a histogram.

• ###### Re: Linest_b, linest_m, tables and multiplications

What would the result look like? Would you be able to draw a picture with the numbers you expect to see?

• ###### Re: Linest_b, linest_m, tables and multiplications

Sure!

we have those data

# this is a "load*inline" in R:

x <- c(3,4,5,7)

y <- c(3,5,9,1)

# here the model in R

model <- lm(y~x)

#results intercept is the result of Qlik linest_b, x is the result of linest_m. (DONE!)

Coefficients:

(Intercept)            x

6.9429      -0.5143

#here the residuals: the result is equal to the  Qlik formula x-(linest_b( total x,y))-(LINEST_M(total x,y))*y (DONE!)

residuals(model)

1          2          3          4

-2.4000000  0.1142857  4.6285714 -2.3428571

# Here what I want: an histogram of the residuals. i.e.  (TO DO!)

hist(residuals(model))

With a quick and dirty result.

• ###### Re: Linest_b, linest_m, tables and multiplications

Trying to understand how you got this.

What exactly is 1, 2, 3, 4? Where did these values come from? I have not used this in a while, so you might have to explain a little more here

• ###### Re: Linest_b, linest_m, tables and multiplications

Sorry for the delay!

They indicate each point of the model, in this case we have four units, so there are four residuals (1,2,3,4).

Residuals (the values under 1,2,3,4) are the difference between the estimates value of the regression (i.e. the points of the line of the regression for each unit) and the real value of the points.

They are one of the thing to look at to see if the regression is a good model or a bad one. For exaple is useful to see if they are distributed like a Normal variable, and the easiest way (not perfect) is to put them in an Histogram.

The formula should be something like (in our case)

y-(6.9429)-(-0.5143)*x

In Qlik Sense

y-(linest_b( total y,x))-(LINEST_M(total y,x))*x  // with your help

Where y and x are the data vector.

The result is the vector that you got after residuals(model).

(I'm going to edit some post, due I've inverted x and y in the model, but the reasoning is te same).

• ###### Re: Linest_b, linest_m, tables and multiplications

Check this out

• ###### Re: Linest_b, linest_m, tables and multiplications

This is pretty awesome, I knew it, but I having it in this post great. Also, if you add a simple variable and use the extension that allows to put inputs, you can vary the number of the class a bit more dynamically.

My idea is to use the official "histogram".

I'll use your idea, I need a result and it is great, but in your opinion, why using the histogram (official one) is so problematic? It is quite absurd to use such a "complex" workaround!