cumulative total

[olivierrobin]

hello,

i have a pivot table in which i want to calculate a cumulative total.

there are two dimensions : group and product

there are two columns : qty, whiwh is a quantity, and rank whiwh is the rank.

I want to compute the cumulative total of qty.

in the example given, there are 3 totals

1st value =  492128

2nd = 213630

3rd = 460939

the need is to have

1st = 492128

2nd = 492128+213630

3rd = 492128+213630+460939

as read in the community, i tried the above fucntion, bur it seems to work only at the detail level, not with dimensions

[olivierrobin]

finally, the answer is :

if(Dimensionality()=1

        , if(AGGR(IF(Rank(sum(QTECDE), 1)<=10, '1',IF(Rank(sum(QTECDE),

1)<=20,'2','3')), KEY) = 1

                , sum({<KEY = {$(=Concat(AGGR(IF(Rank(sum(QTECDE), 1)<=10, chr(39) & KEY &

chr(39)), KEY), ','))}>} total QTECDE) 

                , if(AGGR(IF(Rank(sum(QTECDE), 1)<=10, '1',IF(Rank(sum(QTECDE),

1)<=20,'2','3')), KEY) = 2

                        , sum({<KEY = {$(=Concat(AGGR(IF(Rank(sum(QTECDE), 1)<=20, chr(39) & KEY &

chr(39)), KEY), ','))}>}total QTECDE)

                        , sum({<KEY = {$(=Concat(chr(39) & KEY & chr(39), ','))}>}total QTECDE)

                        )

        ), RangeSum(sum(QTECDE), above(total sum(QTECDE), 1, RowNo(total)))

)

and it works !!!!!!

thanks to Albert.Tran