3 Replies Latest reply: May 16, 2016 6:24 AM by andrii smirkin RSS

    Accumulative Sum in Script

      hi guys,

      I have

      my data goes from 2007 untill today.

      actually I have in my Expressions the accumulative sum- > Range(above(sum(Amount_actual)),0,rowno()) ..

       

      But that depends on the first Amount ..

      how can I get the accumulated amounts on each date  in the script -> get the stock value accumulated on each day ..

       

      thanks forward

        • Re: Accumulative Sum in Script
          m w

          If your data is ordered by date ascending, you can do something like:

           

          Rangesum(StockValue,peek('StockValue',-1) )as StockAccum;

          • Re: Accumulative Sum in Script
            andrii smirkin

            Hi, I had similar task and tried to find solution here. I didn't find it, but one of discussion helped me to solve this problem. I understand that it was almost 1 year ago, but it can be helpful for further work .

             

                    BaseInfo:

                    LOAD

                        RowNo() as NumberOfRow,

                        Parameter1,

                        Amount

                    Resident

                        PreLoadedTable

                    Order By

                        Amount desc;

                 

                    LET varAmountOfParameter1 = FieldValueCount('Parameter1');

             

                    For i = 1 to varAmountOfParameter1

                     

                        varParameter1      = FieldValue('Parameter1',i);

                        varNumberOfRow  = FieldValue('NumberOfRow',i);

             

                        AccumulativeSums:

                        LOAD

                            '$(varParameter1)'  as varParameter1,

                            Sum(Amount)       as AmountAccumulatively

                        Resident

                            BaseInfo

                        Where

                            NumberOfRow <= '$(varNumberOfRow)'

                        Group By

                            '$(varParameter1)'; 

                    NEXT

                 

            NoConcatenate

             

                    ResultTable:

                    LOAD

                        varParameter1,

                        Amount

                    Resident

                        BaseInfo;

                 

                    Join

                 

                    LOAD

                        varParameter1,

                        AmountAccumulatively

                    Resident

                        AccumulativeSums;

                 

                    DROP Tables AccumulativeSums, BaseInfo;

             

            As a result we obtain additional column with accumulative amount. I hope it will help .