Sorting with Above

[agigliotti]

Hi,

I have a table like this:

LOAD * INLINE [
Customer, Unit of measure, Quantity variance YoY
A, BO, 18500
A, MQ, 6200
B, KG, 22100
B, MQ, 7400
C, MQ, 4800
C, NR, -4600
D, MQ, -4100
];

The desired result in the UI is a table as below:

Customer - Unit of measure - Quantity variance YoY - Variance accumulated
B - KG - 22100 - 22100
A - BO - 18500 - 40600
B - MQ - 7400 - 48000
A - MQ - 6200 - 54200
C - MQ - 4800 - 59000
D - MQ - -4100 - 54900
C - NR - -4600 - 50300

I tried with the below expression for Variance accumulated:

Rangesum(above(total column(1),0,RowNo(TOTAL)))

on Customer sorting expression:

sum(Quantity variance YoY)

but i got the below table result:

Could someone help me?

Many thanks in advance for your time.

Best Regards

@rwunderlich @Oleg_Troyansky @Anil_Babu_Samineni @marcus_sommer @sunny_talwar @swuehl @Kushal_Chawda 

[Oleg_Troyansky]

Well, your formula appears to be working as expected, it does the math correctly. The only difference between the "desired output" and the table is in the sorting order. Your "desired result" appears to be sorted by the variance in descending order, while your table is sorted by Customer and U/M. If you sort your table by the variance, you should see exactly the same picture as in your desired result. 

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[agigliotti]

How can I sort the table by the variance in the UI?

[agigliotti]

The only way i found to get the desired result is using a calculated dimension (Customer&'-'&Unit of measure) instead of Customer.
But of course i don't want to show the Unit of measure with the customer in the same column...

[Oleg_Troyansky]

I'm a bit confused, not sure if I understand the challenge. Why not just promote the Expression for the variance to have the first priority in the Sorting tab of Properties?

[agigliotti]

Hi @Oleg_Troyansky ,

I just tried to sort numerically descending on variance and make it first in the sorting list but the table sorting is not affected because of Above function in the measure expression.

[marcus_sommer]

I'm not sure if there are (practically) ways to get your wanted expression-results with the intended sorting - without using a calculated dimension. But this dimension mustn't be visible else you could hide it with the options from the presentation tab (at least by using a straight-table or a bar-chart - a pivot does not provide this option). 

[agigliotti]

Hi @marcus_sommer ,

I'm pretty agree with you.
Is there some trick to show only a substring (Customer) of the calculated dimension (Customer&'-'&Unit of measure) in a straight table?
Obviously I can't hide it.

[marcus_sommer]

You could hide such calculated dimension:

[agigliotti]

Hi @marcus_sommer ,

 I'm using Qlik Sense but anyway if i hide the calculated dimension the table sorting is not correct.