topic Matching linest_m and linest_b function to auto-generated trendline with manual calculation of linest m and linest b in QlikView

Matching linest_m and linest_b function to auto-generated trendline with manual calculation of linest m and linest b

Anonymous — Tue, 06 Sep 2016 18:51:52 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 = -15.516x + 3105.1

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(Pivot). 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].

What comes out for each [Date] row is m = -2.21664050 This is vastly different from the m value given in the auto-generated linear trendline.

My question is why? I created a dummy QV application to match the example shown in the QV11 "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, . 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=15.516x + 3105.1 equation. 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. Please Go throgh the attached QVW for more details description..

Think advance level

Best regards,

Bob

Re: Matching linest_m and linest_b function to auto-generated trendline with manual calculation of linest m and linest b

swuehl — Tue, 06 Sep 2016 19:26:32 GMT

I can't open QVW files currently, but I think your issue can be resolved using what I've shown in

Matching linest_m and linest_b function to auto-generated trendline

Re: Matching linest_m and linest_b function to auto-generated trendline with manual calculation of linest m and linest b

Anonymous — Wed, 07 Sep 2016 05:40:51 GMT

Hi Stefan,

Good Day

yeah i tired that logic in my QVW file but i am not able to find exact solution(Linest m and Linest b values)

in above link QVW files you mentioned manual calculation of Linest m function but i my Dashboard i am not able to find.

in "Sample File.QVW" dashboard one ava% metric i am getting time instead of numbers could please open the QVW file and Please go through it.

the bellow link is not match my exact requirement. but overall its give perfect idea how to calculate Linest m values.

Great example Stefan and i am hearing your voice again now

Matching linest_m and linest_b function to auto-generated trendline

Could you please Go through "Sample File.QVW".please update the logic according to my requirement.

if it is possible could you please mention linest m and linest b calculation manual in my dashboard

Regards,

Bob