4 Replies Latest reply: Aug 23, 2017 8:42 AM by Antonio Mancini RSS

    Date-Difference Related Calculation

    Sandra Pinto

      Hi Everyone,

      I would much appreciate your help with the following:

      I have a table with a Date Column, CustomerID, ProductID and a Column of Production Percentage for each ProductID.

      This Percentage is for the End of the Month in each line.

      Here is a sample:

       

      DateCustomerIDProductIDProduction % at End of Month
      31/05/201744441070%
      30/06/201744441090%
      31/05/201744442030%
      30/06/201744442060%
      31/05/20179999100%
      30/06/2017999910100%
      31/05/20179999200%
      30/06/20179999200%
      31/07/201799992080%

       

      So, for example, ProductID 10 for customer 4444 was at 70% at the end of May and at 90% production at the end of June.

      That means that During June Production % was 20% (=90% - 70%).

      Another example: ProductID 20 for customer 9999 was at 0% at the end of May and also at the end of June. This product was at 80% production at the end of July. Therefore, during July Production % for this product was 80%.

       

      What I would like to ask is what kind of calculation should I use to create this difference of % in production for each product?

      Here is how I would like the Difference to show, more or less:

       

      CustomerIDProductIDJuneJuly
      44441020%0%
      44442030%0%
      999910100%0%
      9999200%80%

       

      Hope my question is clear.

      and thanks in advance for any response.

       

      Sandra

        • Re: Date-Difference Related Calculation
          Antonio Mancini

          Hi Sandra,

          try like this

          Temp:

          LOAD MonthName(Date) as Month,

               CustomerID,

               ProductID,

               Num([Production % at End of Month]) as %Production

          FROM [lib://File]

          (html, codepage is 28591, embedded labels, table is @1);

          NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),

          RangeSum(%Production,-Peek(%Production)),%Production) as Diff

          Resident Temp Order By CustomerID,ProductID,Month;

          Drop Table Temp;

          Regards,

          Antonio

          • Re: Date-Difference Related Calculation
            Felip Drechsler

            Hi Sandra,

             

            Try the following

             

            x:

            Load * Inline

            [

            Date,CustomerID,ProductID,Production % at End of Month

            31/05/2017,4444,10,70%

            30/06/2017,4444,10,90%

            31/05/2017,4444,20,30%

            30/06/2017,4444,20,60%

            31/05/2017,9999,10,0%

            30/06/2017,9999,10,100%

            31/05/2017,9999,20,0%

            30/06/2017,9999,20,0%

            31/07/2017,9999,20,80%

            ];

             

            NoConcatenate

            data:

            Load

            RowNo() as Id,

            MonthName(Date) as Month,

            *,

            fabs(if(RowNo()=1 or ProductID<>Previous(ProductID),num([Production % at End of Month],'#0,0'),(1-[Production % at End of Month])-(1-previous([Production % at End of Month])))) as NewPercentage;

            Load

            Date,

            CustomerID,

            ProductID,

            [Production % at End of Month]

            Resident x

            order by CustomerID,ProductID,Date;

             

            drop table x;

             

            The above code gives me the picture bellow.Sample.png

             

            Felipe.