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

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

I attached my qvw.

• ###### Re: How to calculate regression line, slope ?

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?

• ###### Re: How to calculate regression line, slope ?

Try using the advanced aggregation with the NODISTINCT qualifier, like  ... aggr( NODISTINCT Avg( ...