Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

3 Replies
m_woolf
Master II
Master II

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

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

Not applicable
Author

does not work

it's order by date asc

smirkinaa
Contributor III
Contributor III

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 .