2 Replies Latest reply: Jun 7, 2016 1:33 PM by Thomas Karner RSS

    Accumulate the Value in Script

      Untitled.jpg

      can you help me, ? i want to accumulate the value in the script, but i don't know how to make it, i tried to use range sum and  peek() but it doesn't work, help me please

        • Re: Accumulate the Value in Script
          Gysbert Wassenaar

          Perhaps like this:

           

          Temp:

          LOAD ItemCode,

               QtyPackingslip,

               Date,

               Product

          FROM [comm182616.qvd] (qvd);

           

          Result:

          LOAD * , if(ItemCode = previous(ItemCode), rangesum(QtyPackingslip,peek([Acc Qty Packingslip])), QtyPackingslip) as [Acc Qty Packingslip]

          RESIDENT Temp

          ORDER BY Product, ItemCode, Date;

           

          DROP TABLE Temp;

          • Re: Accumulate the Value in Script
            Thomas Karner

            Try this (to be modified for your situation)

             

            // assumption: data is on level of %CATEG1_KEY, %CATEG2_KEY, Year, Month
            LOAD
              *,
              If(Year = Peek('Year', -1) AND // if year of current record = year of pevious record and
                %CATEG1_KEY = Peek('%CATEG1_KEY', -1) AND // if same %CATEG1_KEY as previous record and
                  %CATEG2_KEY = Peek('%CATEG2_KEY', -1), // if same %CATEG2_KEY as previous record
              RangeSum(Peek('Sales_Acc', -1), Sales), // previous month + current month
              RangeSum(Sales)) // use only first month of new year
              AS Sales_Acc
            Resident Sales
            ORDER BY %CATEG1_KEY, %CATEG2_KEY, Year, Month; // order records by all categories (%CATEG1_KEY, %CATEG1_KEY), Year and Month to use peek function for accumulation
            


            If this helps you please mark it as helpful or correct.

            Thomas