Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
New-Qlik
Creator II
Creator II

Use Liner Trendline factors 'm' and 'c' to build another graph

Hello everyone,

I have a line chart and in that I am drawing liner trendline and also displaying its  Expression and R2 value.

Now I have to use that expression's  (y=mx+c) factors i.e. m and c inorder to build one more chart.

So my question is can I store that m and c factors in some variable?

Please let me know if there are nay doubts about question.

Thanks
Avneet

9 Replies
Gysbert_Wassenaar

Yes, that's possible. Use the Linest_b and linest_m functions to calculate those values.


talk is cheap, supply exceeds demand
New-Qlik
Creator II
Creator II
Author

Thanks but I am not able to figure our the parameters.

I have two fields

Date and Qty

so in my graph my x axis is date and y axis is sum(qty)

I want to get m and c factors wrt to sum(qty) from third date field .

so

I am writing

Main :
LOAD  date,
      Qty
    
FROM

(ooxml, embedded labels);

Test:
Load
*,
LINEST_B(Qty, date,3) as linest_b
//LINEST_M(Qty,date)

Resident Main;

I have mentioned 3 because I want to get it from third date onwards

but it says invalid expression. Please suggest where I am  wrong.

Gysbert_Wassenaar

Test:

Load

     LINEST_B(Qty, date) as linest_b,

     LINEST_M(Qty, date) as linest_m

Resident

     Main

WHERE date >= ...some_value... // <- change ...some_value... to select only the dates you wan to use in the calculation

     ;


talk is cheap, supply exceeds demand
New-Qlik
Creator II
Creator II
Author

Yes I tried same now.

Main :
LOAD [Row Labels] as date,
    [Sum of Qty] as Qty
   
FROM

(ooxml, embedded labels);

left join
load sum(Qty) as Quantity
Resident Main;


Load

LINEST_M( Quantity, date) as M,
LINEST_B(Quantity, date) as B

Resident Main 
where date  >= '2016022605';

but data is coming wrong .

I have build line chart using dim = if(date  >= '2016022605', date)

expression as Sum(Qty)

liner trendline wrt to sum(Qty)

It is giving me y = -0.16907x +49.124

but through script function linest_m is coming as 0

linest_b = 6633.

I have attached sample data as well, which I am using

Gysbert_Wassenaar

Your Row Labels field is not a date field. That means you should try to use it as one. Instead create a field that turns these values into dates (well, timestamps in this case) and use that. See attached qvw.


talk is cheap, supply exceeds demand
New-Qlik
Creator II
Creator II
Author

HI.

I am not able to open qvw  can you please suggest what needful to be done.

New-Qlik
Creator II
Creator II
Author

also Row Label is in this format= yyyymmddhh  so its starts from 26th feb 2016, 05 is the hour

Gysbert_Wassenaar

Turn your Row Labels field into a timestamp field: timestamp#([Row Label],'YYYYMMDDhh') as MyTimeStamp. Then use that new field as chart dimension and in the linest_* functions.


talk is cheap, supply exceeds demand