topic Re: Matching linest_m and linest_b function to auto-generated trendline in QlikView

Matching linest_m and linest_b function to auto-generated trendline

Wed, 31 Aug 2011 02:41:14 GMT

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

Wed, 31 Aug 2011 16:20:22 GMT

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

swuehl — Wed, 31 Aug 2011 20:25:33 GMT

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

Matching linest_m and linest_b function to auto-generated trendline

Fri, 02 Sep 2011 15:14:31 GMT

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

swuehl — Fri, 02 Sep 2011 15:48:41 GMT

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

Anonymous — Thu, 25 Jun 2015 12:25:26 GMT

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

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

swuehl — Thu, 25 Jun 2015 16:03:38 GMT

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

Anonymous — Fri, 26 Jun 2015 11:19:45 GMT

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

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

swuehl — Fri, 26 Jun 2015 16:00:16 GMT

Some slight modifications to your expressions.

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

Anonymous — Tue, 30 Jun 2015 14:14:28 GMT

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

Sat, 21 May 2016 22:27:03 GMT

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

linoyel — Tue, 18 Apr 2017 15:45:00 GMT

Hi Stefan,

Could you help me too maybe?

Manual cumulative regression line using Linest_M and Linest_B functions

Thanks,

Linoy

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

Anonymous — Mon, 05 Jun 2017 09:26:23 GMT

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

swuehl — Mon, 05 Jun 2017 22:40:01 GMT

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

Anonymous — Tue, 06 Jun 2017 04:35:14 GMT

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

Anonymous — Tue, 06 Jun 2017 06:17:32 GMT

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

swuehl — Tue, 06 Jun 2017 09:29:33 GMT

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

Anonymous — Wed, 07 Jun 2017 07:35:14 GMT

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

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

matinmanish — Tue, 24 Dec 2019 12:53:42 GMT

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