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 charts using sample data to find the values of the linest functions available in QlikView. The linest functions can be used in the 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 document.
  2. Select Edit Script in the toolbar and enter the following to the script:

    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. Save the script and click Reload to load the data.

Displaying the results from the script calculations

Do the following:

  1. Add a table box on the sheet and select the following fields to be displayed:

    • 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 calculations made in the 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 charts

Do the following:

  1. Create a new line chart by going to Object > New Sheet Object > Chart...:
    • In the Dimensions window, add X as a dimension.
    • In the Expression window, add Sum(Y) as a measure.
    • In the Presentation window, deselect Suppress Zero-Values.

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

  2. For the expression Sum(Y), enable the linear Trend Line feature and check Show Equation.

    This will show QlikView's built-in line of best fit with the linear regression function described.

  3. Compute the linear regression function using the linest_b and linest_m functions by adding a second expression, defined as:

    $(=LINEST_M(Y,X))*ONLY(X)+$(=LINEST_B(Y,X))

  4. Add a straight table on the sheet and add the following as a calculated 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?