3 Replies Latest reply: Mar 10, 2013 3:56 PM by Stefan Wühl RSS

    How to calculate regression line, slope ?

      Dear Qlikview developers,

       

      I have a dashboard which shows the Gasconsumption for every temperature. It is displayed in a scatter chart.
      The user has the option to refine the dataselection up to 4 times and compare them to eachother (by making use of comparative analysis / alternate states) So I have 4 scatter plots in total.

       

      For every scatter-plot I want to calculate the regression line. I'm aware of the fact that I can select a "Linear Trendline" under the tab Expressions. But I want to calculate the line myself, and show it in the chart. I also have a table where some statistic data must be displayed.

      I want to calculate the following values for every line:

      • The slope
      • value when x = -10 (Temperature = -10)

       

      I know that there are Linest_m, Linest_b functions. But I dont know exactly how to apply these functions in my context.
      Can someone help me out?
      Thanks in advance!

       

      I attached my qvw.

        • Re: How to calculate regression line, slope ?
          Gysbert Wassenaar

          You can use the two expressions of your scatter chart aggregated over the dimension:

           

          =LINEST_M(aggr(Avg({TW1}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW1}Cofely.Temperature),Cofely.Id))

          =LINEST_B(aggr(Avg({TW1}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW1}Cofely.Temperature),Cofely.Id))

            • Re: How to calculate regression line, slope ?

              Hi,

               

              I used the Linest_M function for the 'Alpa' expression/column in the Regression Characteristics column:

               

              if(Valuelist(vTWListItem1,vTWListItem2,vTWListItem3,vTWListItem4)='TW1',LINEST_M(aggr(Avg({TW1}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW1}Cofely.Temperature),Cofely.Id)),' ')

              &

              if(Valuelist(vTWListItem1,vTWListItem2,vTWListItem3,vTWListItem4)='TW2',LINEST_M(aggr(Avg({TW2}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW2}Cofely.Temperature),Cofely.Id)),' ')

              &

              if(Valuelist(vTWListItem1,vTWListItem2,vTWListItem3,vTWListItem4)='TW3',LINEST_M(aggr(Avg({TW3}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW3}Cofely.Temperature),Cofely.Id)),' ')

              &

              if(Valuelist(vTWListItem1,vTWListItem2,vTWListItem3,vTWListItem4)='TW4',LINEST_M(aggr(Avg({TW4}($(vActiveUnitField))),Cofely.Id),aggr(Avg({TW4}Cofely.Temperature),Cofely.Id)),' ')

               

              However, I only see the slope of the first row only. Do you know why?