6 Replies Latest reply: Jul 6, 2016 12:29 PM by Stu Morris RSS

    Linest_M and Linest_B ?

    Prashanth Reddy Nasi

      Hi all,

       

      Could anyone please explain me how linest_m and linest_b works in script and chart when my dimension(X) is date column and measure(Y) is Price, which are grouped materials.

      example:

      Load * Inline [

      Material, InvoiceDate, Price,

      A,01/01/2015,60

      A,01/02/2015,30

      A,01/03/2015,40

      B,01/01/2015,12

      B,01/01/2015,18

      B,01/01/2015,10

      B,01/01/2015,16

      ]

           delimiter is ',';


      My main doubt is How Linest work when we have Date column as dimension?

      When given given some numeric valued column as dimension, it's working fine.

       

      Many thanks

      Regards

      Prashanth

        • Re: Linest_M and Linest_B ?
          Stefan Wühl

          As you mentioned, X and Y values need to be numeric, so read your dates in as numbers:

          Why don’t my dates work?

           

          Then, you need to decide what you want to do with multiple price values per date and material as for material B in your sample. I've decided to average these values.

           

          If you want to match the linest_b value the value shown as trend line in the line chart, you need to correct the x-value by subtracting the min value.

           

          Then your script could look like

           

           

          Set DateFormat = 'MM/DD/YYYY';
          
          
          INPUT:
          Load * Inline [
          Material, InvoiceDate, Price,
          A,01/01/2015,60
          A,01/02/2015,30
          A,01/03/2015,40
          B,01/01/2015,12
          B,01/01/2015,18
          B,01/01/2015,10
          B,01/01/2015,16
          ];
          
          
          LEFT JOIN
          LOAD Material, Min(InvoiceDate) as MinDate Resident INPUT GROUP BY Material;
          
          
          LOAD LINEST_M( AvgPrice, InvoiceDate) as M, LINEST_B(AvgPrice, InvoiceDate-MinDate) as B, Material
          GROUP BY Material;
          LOAD Material, InvoiceDate, Only(MinDate) as MinDate, Avg(Price) as AvgPrice
          Resident INPUT
          GROUP BY Material, InvoiceDate;
          
          

           

           

          Which will only give a M / B values for material A, since you only have one X value for material B.

           

          Compare the values for trend line in chart, see attached sample.

          • Re: Linest_M and Linest_B ?
            Prashanth Reddy Nasi

            If I have gaps in date column, how to fill that gaps as empties but not with zeros.

            If I use the same column, I'm not able to get the straight line.

            How can I achieve this?

             

            trend line.png