topic Re: How to calculate regression line, slope ? in QlikView

How to calculate regression line, slope ?

Sat, 09 Mar 2013 19:19:22 GMT

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 — Sun, 10 Mar 2013 10:27:30 GMT

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 ?

Sun, 10 Mar 2013 19:50:21 GMT

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 ?

swuehl — Sun, 10 Mar 2013 19:56:57 GMT

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