Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
danielle_v
Creator
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
Vegar
MVP
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])

View solution in original post

5 Replies
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
MVP
MVP

Try using  aggr() .

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

See attached example.

danielle_v
Creator
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!

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

danielle_v
Creator
Creator
Author

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

🙂