Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

18 Replies
Not applicable
Author

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

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

Not applicable
Author

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

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
Not applicable
Author

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

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
Not applicable
Author

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

Some slight modifications to your expressions.

Anonymous
Not applicable
Author

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!