2 Replies Latest reply: Sep 16, 2014 8:29 PM by Linda Lei RSS

    Rolling Out YTD Average Sales

      Hi All,

       

      I have a question.

       

      Here's the scenario. I am trying to calculate the YTD Avg Sales per month at the data loading

       

      DepartmentIDMonthSalesYTD Avg Sales
      201561/01/2014200.00 200.00
      201561/02/2014300.00 250.00
      201561/03/2014400.00 300.00
      201561/04/2014500.00 350.00
      201561/05/2014400.00 360.00
      201561/06/2014450.00 375.00
      201561/07/2014500.00 392.86
      201561/08/2014600.00

      418.75

       

      for Feb, YTD Avg Sales is the average of Sales per month from Jan - Feb;

      for Aug, YTD Avg Sales is the average of Sales per month from Jan - Aug.

       

      I tried RangeAvg(Above()), it works in charts but does not work in scripts.

       

      Does anyone know how to do the coding?

       

      Thanks.

       

      Linda

       

        • Re: Rolling Out YTD Average Sales
          Manish Kachhia

          Temp:

          Load

            DepartmentID,

            Date(Date#(Month,'D/MM/YYYY')) as Month,

            Sales

          Inline

          [

            DepartmentID, Month, Sales

            20156, 1/01/2014, 200.00

            20156, 1/02/2014, 300.00

            20156, 1/03/2014, 400.00

            20156, 1/04/2014, 500.00

            20156, 1/05/2014, 400.00

            20156, 1/06/2014, 450.00

            20156, 1/07/2014, 500.00

            20156, 1/08/2014, 600.00

            20157, 1/01/2014, 200.00

            20157, 1/02/2014, 250.00

            20157, 1/03/2014, 300.00

            20157, 1/04/2014, 600.00

            20157, 1/05/2014, 400.00

            20157, 1/06/2014, 250.00

            20157, 1/07/2014, 500.00

            20157, 1/08/2014, 300.00

          ];

           

           

          NoConcatenate

          Load

            *,

            TotalSales/ID as AvgSales;

          Load

            *,

            IF(DepartmentID <> Previous(DepartmentID), 1, Peek(ID) + 1) as ID,

            IF(DepartmentID = Previous(DepartmentID), Peek(TotalSales) + Sales, Sales) as TotalSales

          Resident Temp

          Order By DepartmentID, Month;

           

           

          Drop Table Temp;