topic Re: Problems with order and accumulated in tables in App Development

Problems with order and accumulated in tables

DeanW — Fri, 07 Feb 2025 19:57:27 GMT

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

Re: Problems with order and accumulated in tables

rubenmarin — Sat, 08 Feb 2025 17:49:14 GMT

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.

Re: Problems with order and accumulated in tables

DeanW — Sat, 08 Feb 2025 19:26:50 GMT

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

Re: Problems with order and accumulated in tables

rubenmarin — Sun, 09 Feb 2025 08:16:33 GMT

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.

Re: Problems with order and accumulated in tables

DeanW — Mon, 10 Feb 2025 15:23:31 GMT

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?

Re: Problems with order and accumulated in tables

rubenmarin — Mon, 10 Feb 2025 17:51:18 GMT

@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.

Re: Problems with order and accumulated in tables

DeanW — Mon, 10 Feb 2025 18:55:50 GMT

@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!