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]

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

[swuehl · Accepted solution]

Hi Chris,

hope I understood your problem correctly.

I used

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

as dimension to convert your Daterange into consecutive numbers 0,1,2,3... and adapted your manual calculation accordingly.

Hope this helps,

Stefan

[#4]

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

[swuehl]

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.

[Anonymous]

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.

Appreciate any help you can give on this!

Graham

[swuehl]

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?

[Anonymous]

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.

Thanks for your help!

Graham

[swuehl]

Some slight modifications to your expressions.

[Anonymous]

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!