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. The linest functions can be used in the data load script and in chart expressions.

Please refer to the individual linest chart function and script 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.

Displaying the results from the data load script calculations

1. Do the following:

   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 table, and from Fields add the following as columns:

   • Linest_B
   • Linest_DF
   • Linest_F
   • Linest_M
   • Linest_R2
   • Linest_SEB
   • Linest_SEM
   • Linest_SEY
   • Linest_SSREG
   • Linest_SSRESID

The table containing the results of the linest calcuations made in the data load script should look like this:

Creating the linest chart function visualizations

Do the following:

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