topic Re: Rank - Ingoring Selections in QlikView

Rank - Ingoring Selections

danielle_v — Tue, 03 Sep 2013 13:16:58 GMT

Hi All,

I'm trying to use the rank function in order to produce a 'supplier scorecard'.

Supplier	Sales	Rank
A	1000	2
B	500	3
C	2000	1
D	250	4

In the above example, I have used the rank function in order to rank the suppliers by sum(Sales). So, supplier B is ranked 3 out of 4 suppliers.

However, the problem comes when I want to be able to select just one supplier to view just their data. If selecting supplier B, I would just want to see the following;

Supplier	Sales	Rank
B	500	3

However, in my application this is showing rank 1 - I assume this is becuase by selecting the supplier, I am effectively ranking supplier B as 1 of 1.

I have tried using TOTAL, but just can't get this to work as I want it to.

Any help greatly appreciated!

Re: Rank - Ingoring Selections

Nicole-Smith — Tue, 03 Sep 2013 14:44:06 GMT

Instead of using rank(), create a variable (such as vSales) with the following expression:

=concat({1}aggr(sum({1}Sales), Supplier), ',', -aggr(sum({1}Sales), Supplier))

Then, replace the rank() expression on your chart with:

SubstringCount(left(vSales, index(vSales, sum(Sales))), ',')+1

See attached for working .qvw file.

Re: Rank - Ingoring Selections

Vegar — Tue, 03 Sep 2013 14:44:34 GMT

Try using aggr() .

aggr( rank(total sum({1} [Sales])),[Supplier])

See attached example.

Re: Rank - Ingoring Selections

danielle_v — Tue, 03 Sep 2013 15:19:50 GMT

Many thanks for your quick response, this is very useful!

However, I have now stupidly realised that I need to be able to take into account some other selections, such as year. For example, if Supplier 2 was ranked number 4 overall, but number 2 in 2012, I would want this to show as rank 2 when selecting Supplier 2 and 2012.

Is there any way of factorign this into the expression?

Thanks again!

Re: Re: Rank - Ingoring Selections

Vegar — Tue, 03 Sep 2013 15:34:13 GMT

Instead of using the <1> you can use $ with a modifier, explicit canceling all dimension selections except the ones you want to keep. In your case the supplier.

aggr( rank(total sum({$<dim1=, dim2=, dim3= >} [Sales])),[Supplier])

Re: Rank - Ingoring Selections

danielle_v — Tue, 03 Sep 2013 15:48:29 GMT

This is exactly what I need, thank you so much for your help!!

🙂