Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
3 Replies
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Not applicable

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?

MVP
MVP

Re: How to calculate regression line, slope ?

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