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:

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.