Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sort chart by two expressions

I have a simple bar chart with one dimension: Supplier.

And one dimension: Percentage of Good Deliveries.

At the moment I am sorting by expression: the percentage of good deliveries for the supplier (set analysis). However if a supplier has delivered 1 order, and another supplier has delivered 500; then I want the supplier with 500 to come above the supplier with 1 order.

Is there a way to first sort by percentage of good deliveries, (and if the percentage is the same as another value in the chart e.g. two suppliers are 100%) then sort by count (number of orders)?

So: Sort by percentage of good deliveries

If the percentage is the same as another supplier

then sort the matching values by order quantity

Thanks

1 Solution

Accepted Solutions
consenit
Partner - Creator II
Partner - Creator II

OK, got it.

Try using the function Rank() in the sorting expression, something like:

=SUM(Percentaje)*10000 - Rank(Deliveries)

Kind Regards,

Ernesto.

View solution in original post

5 Replies
consenit
Partner - Creator II
Partner - Creator II

Hello.

Try using a compound formula like:

sum(Percentaje) * 10000 + sum(Deliveries)

10000 is an arbitrary value larger than the maximum possible value for "Deliveries".

See QVW attached.

Kind Regards,

Ernesto.

Not applicable
Author

Thanks for the reply Ernesto, we had considered something like this however when sorting and the count of orders becomes ~10000 then the 30% jumps above the 50% values.

In the example 'D' should come below 'A' but above 'B'.

As 'D' and 'B' are both 30% but 'D' has more orders.

Kind Regards,

Callum

consenit
Partner - Creator II
Partner - Creator II

OK, got it.

Try using the function Rank() in the sorting expression, something like:

=SUM(Percentaje)*10000 - Rank(Deliveries)

Kind Regards,

Ernesto.

Not applicable
Author

Thanks Ernesto, that seems to work.

So I changed your sort expression from:=SUM(Percentaje)*10000 - Rank(Deliveries)


Update: Have attached working version with Set Analysis also.

Slight bug in that if the percentages are very similar and one vendor has a lot more orders then it will rank higher, not a bad thing but depends on context:

SupplierTotal OrdersOTIF %
ABC50020
XYZ4021
Eg30015


Anonymous
Not applicable
Author

Hi

I have same requirement but need to consider  4 columns .

if first column value is same then consider second column

if second column also have same value then consider 3rd column

if third column also have same values then consider 4 column.

Could you please help on this .

Thanks in advance

BKC