Rank in Textbox as KPI

[wonkymeister]

Afternoon all!

i need to use the Rank function in a textbox as a KPI

i.e. without the dimension - just as an expression. i only want to display the rank of a single product.

For example i have 20 products with sales volume. i want to display in a text box the rank that one particular product is at. I've got this working fine in a chart with a dimension - but i have no idea how to script this as a single expression.

so as a chart this works perfectly:

Dimension: Products

Expression: =rank(sum(Sales_Volume))

but how do i go about writing the expression to take into account the dimension?

cheers!

[Anonymous]

Hi,

Try:

=aggr(if(rank(sum(Sales))=1,Product),Product)

Regards

Neetha

[wonkymeister]

thanks but that displays the number 1 product.

i want to display the rank for a particular product which i'll hardcode into the expression.

[jvitantonio · Accepted solution]

In your case, you'll need to select the product you want to display. if you always need to see the rank for a particular product, then you need to place the product in the expression using set analysis.

Ex:

aggr(rank(TOTAL sum({1 <Product = {'B'}>} Sales)), Product)

Please find the solution in the attached file.

JV

[wonkymeister]

thanks QL - works great. any tips on how i would hard code a particular product into the set analysis?

[ThornOfCrowns]

QL has shown you how to do this. Replace <Product={'B'}> with your product information.

[#7]

thanks for this very useful post

[wonkymeister]

thanks James-  missed that.

never do anything tricky on a Friday

[Vimarsh]

Hi JV,

Thanks for the solution but its not working for me. I loaded your Test.qvw in my Qlik Sense Desktop.

Product	Sum(Sales)
B	180
A	10
D	8
C	5

All expressions are giving me '1'

aggr(rank(TOTAL sum({1 <Product = {'A'}>} Sales)), Product) returns '1' (Expected '2')
aggr(rank(TOTAL sum({1 <Product = {'B'}>} Sales)), Product) returns '1'  (Expected '1')
aggr(rank(TOTAL sum({1 <Product = {'C'}>} Sales)), Product) returns '1' (Expected '4')
aggr(rank(TOTAL sum({1 <Product = {'D'}>} Sales)), Product) returns '1' (Expected '3')

Am I doing something wrong? Your help is appreciated!

Thank You!
VS