17 Replies Latest reply: Jun 7, 2017 3:35 AM by Sanjyot Paktar

# Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Here is a test application that shows what I'm seeing.  I can confirm that when QlikView adds the trendline it is changing the dates on my x-axis to the values 0, 1, 2, etc.  When I use the linest_m() and linest_b() functions QlikView is using the actual numerical value of the dates instead (40634, 40603, 40575, etc).

At the end of the day the results from y=mx+b are pretty close.  They aren't exactly the same, but close enough.  I guess I'd just rather be able to reproduce the QlikView-generated trendline because it has a nicer y-intercept.  You'll see in the app that their line crosses at y = 733.5 and my derived line crosses at 160,034.1715.

If anyone had thoughts on how to dynamically convert my date dimension into a value of 0, 1, 2, etc. for use in the linest_m() and linest_b() equations that would be good to know.

Chris

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Hi Chris,

hope I understood your problem correctly.

I used

=aggr(rank(-X2),X2)-1

Hope this helps,

Stefan

• ###### Matching linest_m and linest_b function to auto-generated trendline

Thanks!  That works very well.  I was surprised that I couldn't use this new calculated dimension in the linest_m and linest_b functions but it worked fine with the manual calculation (as you saw).

I appreciate the help!

Chris

• ###### Matching linest_m and linest_b function to auto-generated trendline

Chris,

I believe the linest_X functions only take a field as parameter where you need to put the x-Values in, no calculated dimension.

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Hi Swuehl,

I know this was some time ago but I've used your example and it looks great.

I'm having a slight problem with calculating N, there any light you can shed on this?

Also I don't suppose you've done the same with manual calculating Intercept? Having some troubles with the difference between linest_m and linest_b when compared to their equivalents in Excel.

Graham

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Oh, yes, it has been a while since then...

What's your issue with calculation of N?

Could you upload some data, like a sample QVW and your Excel calculations?

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Thanks for the reply swuehl, sorry took a bit of time putting together the example.

I think I've got N now actually, the distinct number of known y's?

I've attached a qvw with the calculations and an excel file with the data and also expected excel values.

Currently getting a positive value when using your manual calculation for slope.

Graham

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Some slight modifications to your expressions.

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Thanks swuehl, perfect!

I've attached the latest version where I've added the Intercept calculation too. This now exactly replicates Excel.

Cheers for your help on this!

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

@@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.

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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.

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

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

• ###### Re: Matching linest_m and linest_b function to auto-generated trendline

Hi Stefan,

Could you help me too maybe?

Manual cumulative regression line using Linest_M and Linest_B functions

Thanks,

Linoy