Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Sort chart by two expressions

OK, got it.

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

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

Kind Regards,

Ernesto.

5 Replies
consenit
Contributor II

Re: Sort chart by two expressions

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

Re: Sort chart by two expressions

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

Re: Sort chart by two expressions

OK, got it.

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

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

Kind Regards,

Ernesto.

Not applicable

Re: Sort chart by two expressions

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


balkumarchandel
Valued Contributor II

Re: Sort chart by two expressions

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

Community Browser