Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
DeanW
Partner - Contributor II
Partner - Contributor II

Problems with order and accumulated in tables

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.

DeanW_0-1738957736864.png

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.

DeanW_1-1738958066659.png

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.

DeanW_2-1738958106926.png

What can you do!

Please!!!

 

Regards

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
rubenmarin1

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.

View solution in original post

6 Replies
rubenmarin1

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
Partner - Contributor II
Partner - Contributor II
Author

Hi, @rubenmarin1 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

rubenmarin1

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
Partner - Contributor II
Partner - Contributor II
Author

Hi, thank you @rubenmarin1, 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?  

rubenmarin1

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

And you also need the other solution to sort by quantity

rubenmarin_0-1739209669520.png

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:

rubenmarin_1-1739209766796.png

But the Above TOTAL works in both cases.

DeanW
Partner - Contributor II
Partner - Contributor II
Author

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