Announcements
cancel
Showing results for
Did you mean:
Creator

## Rank - Ingoring Selections

Hi All,

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

SupplierSalesRank
A10002
B5003
C20001
D2504

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;

SupplierSalesRank
B5003

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!

1 Solution

Accepted Solutions
MVP

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

5 Replies
MVP

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.

MVP

Try using  aggr() .

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

See attached example.

Creator
Author

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!

MVP

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

Creator
Author

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

🙂

Community Browser