Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
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))


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
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))


talk is cheap, supply exceeds demand
Not applicable
Author

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?

swuehl
MVP
MVP

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