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

    Matching linest_m and linest_b function to auto-generated trendline

    Chris Perry

      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