Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi,
Try:
=aggr(if(rank(sum(Sales))=1,Product),Product)
Regards
Neetha
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.
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
thanks QL - works great. any tips on how i would hard code a particular product into the set analysis?
QL has shown you how to do this. Replace <Product={'B'}> with your product information.
thanks for this very useful post
thanks James- missed that.
never do anything tricky on a Friday
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