Running total per dimension

[Dimitar]

Hello,

I have the following problem building running totals per dimension in a table. I need for each project in column A to build a running total like shown in column E. I've already tried with rangesum and aggr but I cannot achieve the desired result.

Best regards,

Dimitar

[AshutoshBhumkar]

Hi,

Try below expression in table:

Rangesum(Above(Sum(Amount),0,RowNo()))

[Channa]

Hi Bro,

this expression doing running total by years and projects

what if want to do by only project?

[pradosh_thakur]

Add a new column result in script

if(peek(Project)= Project,Peek(Results)+Amount,Amount) as Results

[Dimitar]

Hi,

thank you but that does not work. It makes the result per project and year. I need it per project.

BR

Dimitar

[pradosh_thakur]

Hi

the expression should work. Can you please post the out put you are getting .

[Dimitar]

Hi,

thank you. I will try it. My problem is that my data is not so simple as the example I gave to illustrate my problem 🙂

BR

Dimitar

[Dimitar]

With peek it will work,  but the rangesum does not work

[pradosh_thakur]

Are you getting the answer with peek. If yes please close the thread else let us know if you need any more help.

[kaanerisen · Accepted solution]

Hi Dimitar,

If you want to achieve this result on front-end, your rangesum function needs to edit little bit 🙂

I used the sample data below for creating the issue

Load * Inline
[
ID,YEAR,YEARMONTH,Type,Sale
111,2017,201703,'Cash Out',454
111,2017,201705,'Cash In',54
111,2018,201801,'Cash In',21
111,2018,201802,'Cash Out',12
111,2018,201803,'Cash In',121
222,2017,201703,'Cash In',1
222,2017,201705,'Cash Out',12
222,2017,201706,'Cash In',15
222,2018,201802,'Cash Out',121
222,2018,201803,'Cash In',51
333,2017,201703,'Cash Out',21
333,2017,201705,'Cash Out',212
333,2017,201706,'Cash In',15
333,2017,201707,'Cash In',1541
333,2017,201708,'Cash In',454
];

Expression for running sum over one dimension (According the data above):

RangeSum(Above(TOTAL Sale,0,AGGR(RowNo(),ID,(YEARMONTH,(NUMERIC,ASCENDING)))))

You can check the attached qvf.

Hope it helps..