topic Re: Sort chart by two expressions in QlikView

Sort chart by two expressions

Wed, 10 Dec 2014 09:32:00 GMT

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

Re: Sort chart by two expressions

consenit — Wed, 10 Dec 2014 13:12:03 GMT

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.

Re: Sort chart by two expressions

Wed, 10 Dec 2014 14:24:04 GMT

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

Re: Sort chart by two expressions

consenit — Wed, 10 Dec 2014 15:11:19 GMT

OK, got it.

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

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

Kind Regards,

Ernesto.

Re: Sort chart by two expressions

Thu, 11 Dec 2014 10:46:07 GMT

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:

Supplier	Total Orders	OTIF %
ABC	500	20
XYZ	40	21
Eg	300	15

Re: Sort chart by two expressions

Anonymous — Fri, 08 May 2015 05:47:47 GMT

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