Matching linest_m and linest_b function to auto-generated trendline

[#1]

Hi,

I have a set of date with [Date] on the x-axis and [Score] on the y-axis.  When I plot the data as a bar chart and add a linear trendline I get the following auto-generated line equation:

y = 0.25368x + 81.368

I'd like to pull the slope (m) and y-intercept (b) out of that equation so I can use it in a straight table.  Following the documentation and the many posts on this forum regarding this subject I added the following expressions to a straight table.  This straight table has a single dimension - [Date].

linest_m(Total Aggr(Sum(Score), Date), Date)

linest_b(Total Aggr(Sum(Score), Date), Date)

What comes out for each [Date] row is m = 0.044559106 and b = -1729.108.  This is vastly different from the m and b values given in the auto-generated linear trendline.

My question is why?  I created a dummy QV application to match the example shown in the QV9 "Nested Aggregations and Related Issues" online help (which can be accessed via a link in the online help for the Aggr() function) and I was able to get the trendline, linest_m() and linest_b() functions to behave properly.  Is there an issue with using dates as the x-axis for these regression calculations?  I assume that QV is somehow converting these dates into values - for the y=0.25368x + 81.368 equation the line plots properly if I use x-values of 1,2,3,etc.  I'm not sure what is being used for x when I try to use linest_m and linest_b, though.

Just wondering if anyone has run into this before or if there is any insight.  Unfortunately I cannot share my application as it contains protected health information.  I have created a dummy application where I can duplicate this problem which I can upload if anyone would like.

Chris

[#2]

I'm having the same problem getting Linest and Excel to match... I'm using the free QV personal edition and therefore can't open your example .qvws... can you post your slope and intercept expression calculations so I can see them? Thanks! swuehl‌

[linoyel]

Hi Stefan,

Could you help me too maybe?

Manual cumulative regression line using Linest_M and Linest_B functions

Thanks,

Linoy

[Anonymous]

@@Hi Stefan

Although this post is really old I have found your solution really useful to develop customized trend lines.

I followed your steps and i was able to develop m value successfully as the auto generated trend line function.

I am attaching my qvw file .

What i havent understood is that when i use m*X2Value + Y2 to plot the trend line this value doesn't seem to match the graph value. Am i doing something wrong while adding the intercept?

Your suggestions will be really helpful as I have been stuck with this for a really long time.

[swuehl]

You need to calculate the y intercept as shown in Grahams post (and as I added to your QVW).

[Anonymous]

Thanks Stefan..

Looks like i got all mixed up with the intercept calculation from Graham's post. Both the solutions's worked. Thanks for the assistance as this was a long stuck issue.

[Anonymous]

Just one more question.. Why does it make me select the months for the m value and intercept to be calculated properly? If i dont select the month it displays incorrect values. I replaced Month with Last12Months. Y value is calculated correctly but the m value and intercept shows right values only if the 12 months selected in the listbox..

[swuehl]

That's because the advanced aggregations in the Ey and Avg Y calculations do take NULL in its dimension into account:

So you need to filter on the Last12Months dimension values only:

Ey:

=sum({<Last12Months = {"*"}>}

total(aggr(count (DISTINCT {<[Referred to CPS]={'Y'},DateType={'Created','Created_RTC','Created_Regulatory_Static_2016'},Revised_Priority = {'1. High','2.1 Medium - Multiple Customer Impact','2.2 Medium - Critical care Customer Impact','1.1 Critical','1.2 Critical','2.1 High','2.2 High','1. Critical','2. High','3.1 Medium - Urgent'}>}[TR #]),Last12Months)))

Avg Y:

=Avg({<Last12Months = {"*"}>} TOTAL

Aggr( count( distinct {<[Referred to CPS]={'Y'},DateType={'Created','Created_RTC','Created_Regulatory_Static_2016'},Revised_Priority = {'1. High','2.1 Medium - Multiple Customer Impact','2.2 Medium - Critical care Customer Impact','1.1 Critical','1.2 Critical','2.1 High','2.2 High','1. Critical','2. High','3.1 Medium - Urgent'}>}[TR #]),Last12Months))

[Anonymous]

Thanks Stefan once again..made the changes accordingly...

[matinmanish]

Hi,

My function is working correctly but there are 3 different calendars linked to dimension.

Issue:- When by default a calendar is set and I implement the linest function it shows blanks for the other 2 calendars.

Have attached the graph for your reference.

My question is how do we get the function working on dynamic period(Month, Day, Year), dimension(3 calendars linked(Financial, Standard, CSS).

It should work when I select the different calendars or different period.

Any help is highly appreciated.

Thanks