Cumulative Sum based on dynamic Ranking

[jorgie]

Hi to all,

I try to cumulative sum values based on calculated Rank in front end.

More specific, I have  value Rank( Sum ( if ( Sales>0, Sales ) ) ).

Based on the value of each row I want to have the cumulative sum. 

For example if rank is 3, must sum Sales for Rank 1, 2, 3.

The RowNo() read import sort and give wrong results in general.

Thank you in advance for your help

[Kushal_Chawda · Accepted solution]

@jorgie  try below expression. Where ID is the dimension of your table. Replace it with actual dimension

=sum(aggr(rangesum(above(total sum(Sales),0,RowNo(total))),(ID,(=sum(Sales),DESCENDING))))

[jorgie]

hi @Kushal_Chawda , thanks for replying

Unfortunately doesn't summarize cumulatively based on rank

Using RowNo seems to make the sum based on import row series, and also there are 0

thanks again

[Kushal_Chawda]

@jorgie  it's because you are using wrong ID in your expression. You have used Capital ID whereas your actual field name is id

[jorgie]

id is the dummy, I use the actual and correct field name which is [Customer_KUNAG.NAME1_KUNNR_NEW]

[Kushal_Chawda]

@jorgie  then you need to use that field name in place of ID column in expression

[Kushal_Chawda]

@jorgie  you expression will be

=sum(aggr(rangesum(above(total sum(Sales),0,RowNo(total))),([Customer_KUNAG.NAME1_KUNNR_NEW],(=sum(Sales),DESCENDING))))

[jorgie]

i used dummy at description ...

=sum(aggr(rangesum(above(total sum(Miktes),0,RowNo(total))),([Customer_KUNAG.NAME1_KUNNR_NEW],(=sum(Miktes),DESCENDING)))) 

the exact format of expression.

Is RowNo a must? I believe that creates the issue

[Kushal_Chawda]

@jorgie  RowNo is must for accumulation. Without looking at sample app I can't comment what's wrong.

[jorgie]

i created a simple table  and honestly works

the thing is that isnt possible to share a sample. its complicated data from erp.
all data are have customer, product and time dimensions in table.

I think I will accept it as a solution because in a way it works. I must check in depth about my data base.

thanks