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:

Linest_B Linest_DF Linest_F Linest_M Linest_R2 Linest_SEB
-35.047 18 20.788 8.605 0.536 22.607

Linest_SEM Linest_SEY Linest_SSREG Linest_SSRESID
1.887 48.666 49235.014 42631.186

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

Linest functions Linest function results
Linest_b -35.047
Linest_df 18
Linest_f 20.788
Linest_m 8.605
Linest_r2 0.536
Linest_SEB 22.607
Linest_SEM 1.887
Linest_SEY 48.666
Linest_SSREG 49235.014
Linest_SSRESID 42631.186

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?