An example of how to use linest functions

The linest functions are used to find values associated with linear regression analysis. This section describes how to build visualizations using sample data to find the values of the linest functions available in Qlik Sense. Please refer to the individual linest chart function topics for descriptions of syntax and arguments.

Loading the sample data

Do the following:

1. Create a new app.

2. In the data load editor, enter the following:

   T1:

   LOAD *, 1 as Grp;

   LOAD * inline [

   X |Y

   1| 0

   2|1

   3|3

   4| 8

   5| 14

   6| 20

   7| 0

   8| 50

   9| 25

   10| 60

   11| 38

   12| 19

   13| 26

   14| 143

   15| 98

   16| 27

   17| 59

   18| 78

   19| 158

   20| 279 ] (delimiter is '|');

   R1:

   LOAD

   Grp,

   linest_B(Y,X) as Linest_B,

   linest_DF(Y,X) as Linest_DF,

   linest_F(Y,X) as Linest_F,

   linest_M(Y,X) as Linest_M,

   linest_R2(Y,X) as Linest_R2,

   linest_SEB(Y,X,1,1) as Linest_SEB,

   linest_SEM(Y,X) as Linest_SEM,

   linest_SEY(Y,X) as Linest_SEY,

   linest_SSREG(Y,X) as Linest_SSREG,

   linest_SSRESID(Y,X) as Linest_SSRESID

   resident T1 group by Grp;

3. Click l to load the data.

Creating the linest chart function visualizations

1. In the data load editor, click ” to go to the app view, create a new sheet and open it..

2. Click @Edit to edit the sheet.
3. From Charts add a line chart, and from Fields add X as a dimension and Sum(Y) as a measure.

   A line chart is create that represents the graph of X plotted against Y, from which the linest functions are calculated.

4. From Charts add a table with the following expressions as a dimension:

   ValueList('Linest_b', 'Linest_df','Linest_f', 'Linest_m','Linest_r2','Linest_SEB','Linest_SEM','Linest_SEY','Linest_SSREG','Linest_SSRESID')

   This uses the synthetic dimensions function to create labels for the dimensions with the names of the linest functions. You can change the label to Linest functions to save space.

5. Add the following expression to the table as a measure:

   Pick(Match(ValueList('Linest_b', 'Linest_df','Linest_f', 'Linest_m','Linest_r2','Linest_SEB','Linest_SEM','Linest_SEY','Linest_SSREG','Linest_SSRESID'),'Linest_b', 'Linest_df','Linest_f', 'Linest_m','Linest_r2','Linest_SEB','Linest_SEM','Linest_SEY','Linest_SSREG','Linest_SSRESID'),Linest_b(Y,X),Linest_df(Y,X),Linest_f(Y,X),Linest_m(Y,X),Linest_r2(Y,X),Linest_SEB(Y,X,1,1),Linest_SEM(Y,X),Linest_SEY(Y,X),Linest_SSREG(Y,X),Linest_SSRESID(Y,X) )

   This displays the value of the result of each linest function against the corresponding name in the synthetic dimension. The result of Linest_b(Y,X) is displayed next to linest_b, and so on.

Result

Tables for the sample data containing measure using the linest functions would look like this: