Problems with order and accumulated in tables

[DeanW]

Hello everyone.

I would like to ask for your help with a very important topic: I want to create a cumulative total in a table. The problem I have is that when I perform the cumulative calculation, the RowNo() function distorts a column that should be sorted in descending order.

Now I am going to show you a table that is working.

You can see that I have a table of quantity sorted in descending order, your participation and the cumulative participation all in percentages.
Quantity: Sum({<%Flag_Invoice={1}>} QUANTITY * If(IsNull(MENGE.STPO), 1, MENGE.STPO) * Sign)

Cumulative %Participation:
Sum({<%Flag_Flag_Invoice={1}>} QUANTITY * If(IsNull(MENGE.STPO), 1, MENGE.STPO) * Sign)
/
Sum(TOTAL{<%Flag_Invoice={1}>} QUANTITY * If(If(IsNull(MENGE.STPO), 1, MENGE.STPO) * Sign)

%Accum:
RangeSum(Above($(v%_Accumulated_Quantity),0,RowNo(TOTAL)))

Being the variable $(v%_Accumulated_Cant) equal to %Accum Share I did it this way just to make it look neater.

BUT!
When I add an extra dimension, my quantity gets messed up and doesn't look right.

NOTES:
In the sort tab I set the first dimension to sort by expression and the rest I unsorted the sorts and it didn't work either.

What can you do!

Please!!!

Regards

[rubenmarin]

Hi, maybe you also need a TOTAL in your Above function:

RangeSum(Above(TOTAL $(v%_Accumulated_Quantity),0,RowNo(TOTAL)))

Beign a $-expansion i'm not sure if this will work but it's an easy try.

[DeanW]

Hi, @rubenmarin I think you solution not work, I hope my doubt all of them undertand me, my higher problem is when I agree dimension, the column "Cantidad" desorder and I need orden descendent.

Thank yo for you reply !

Regards

[rubenmarin · Accepted solution]

OK @DeanW, then you have 2 issues, the sort by quantity and the fact that the accumulation doesn't accumulates different "Descripcion Material"

The 2nd can be solved as I posted before: adding TOTAL to Above.

The sort order it's a bit more complicated. Once you use the Above function, the sort by column doesn't works and you have to use the dimension sort by expression option. But this is applied to the values of the dimension itself.

So if Golf is the value with most quantity it will go first, doesn't matter the values splitted by the second dimension, it only sorts the first dimension values.

To make the second dimension count when sorting the values you need to split the first dimension values in different values for each combination of dimensions.

I don't know if there is a better way but you can do this switching "Descripcion Material" to a calculated dimension as: =Aggr(Dual("Descripcion Material",RowNo(TOTAL)),"Descripcion Material",CODIGO_MATERIAL)

With this, each combination of the dimension values will get a different number and can be sorted by both dimensions.

[DeanW]

Hi, thank you @rubenmarin, but de not work. I write de aggr() and put the TOTAL in rowno but the problem still there. I dont know what i do. Someone have a new solution?  

[rubenmarin]

@DeanW, the TOTAL should be also in the Above.

And you also need the other solution to sort by quantity

Dim1 is calculated as =Aggr(Dual(Dim1,RowNo(TOTAL)),Dim1,Dim2)

Above is: RangeSum(Above(Sum(Value), 0, RowNo(TOTAL)))

Above TOTAL is RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL)))

Without the calculated dimension for Dim1 it shows as your issue with sorting:

But the Above TOTAL works in both cases.

[DeanW]

@rubenmarin You Are a Genius !!! Safe my lifeee. 

You are right, I must use the AGGR on dimension and total in rowno() above() !!

I’m at a loss for words to express my gratitude!

regards!