Rank - Ingoring Selections

[danielle_v]

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!

[Nicole-Smith]

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.

[Vegar]

Try using  aggr() .

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

See attached example.

[danielle_v]

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!

[Vegar · Accepted solution]

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])

[danielle_v]

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

🙂