Discussions

Linear regression curve fitting

  • 1.  Linear regression curve fitting

    Posted 05-13-2020 19:12
    Edited by Mark Shnier (YQC) 05-13-2020 19:14
    Just thought I would share something that I solved for a client which could be useful to others.  The challenge was to create a Visual Health Score Card for a set of Product Lines so that a Product Manager could do a line review with their manager and quickly assess the health of each Product Line.

    In classical Product Management there is a Product Life Cycle of Initiation, then Growth, then Maturity, and then the inevitable Decline where the Product needs to be phased out and replaced.  The challenge was to represent that with stoplight coding on a report  and also show visually the sales trend.

    On the report below, you can see two green lights for the sale trend based on the last 36 and 24 month sales trend. These have Annualized growth rates of 21% and 12% respectively.  So all good?    In fact No!  The 12 month sales trend however is minus 43% on an Annualized basis.  Those growth numbers actually come from the slope of the linear regression best fit  straight lines for the last 36, 24 and 12 months respectively.  You can see that the red line is going down.

    So the point of this post is that while we do not have native ability to auto calculate those straight lines, it can be done.  It's not a causal undertaking to get this working and not really something I can explain here step by step, but its basically calculating a formula for the least squares method best fit to calculate the values for m and b in the straight line formula   y = mx +b. 
      




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------