topic Re: Rank in Textbox as KPI in QlikView

Rank in Textbox as KPI

wonkymeister — Fri, 12 Dec 2014 15:27:53 GMT

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!

Re: Rank in Textbox as KPI

Anonymous — Fri, 12 Dec 2014 15:37:15 GMT

Hi,

Try:

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

Regards

Neetha

Re: Rank in Textbox as KPI

wonkymeister — Fri, 12 Dec 2014 15:43:17 GMT

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.

Re: Rank in Textbox as KPI

jvitantonio — Fri, 12 Dec 2014 15:45:10 GMT

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.

Re: Rank in Textbox as KPI

wonkymeister — Fri, 12 Dec 2014 15:53:31 GMT

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

Re: Rank in Textbox as KPI

ThornOfCrowns — Fri, 12 Dec 2014 15:56:14 GMT

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

Re: Rank in Textbox as KPI

Fri, 12 Dec 2014 15:58:02 GMT

thanks for this very useful post

Re: Rank in Textbox as KPI

wonkymeister — Fri, 12 Dec 2014 16:03:46 GMT

thanks James- missed that.

never do anything tricky on a Friday

Re: Rank in Textbox as KPI

Vimarsh — Wed, 06 Nov 2019 16:01:13 GMT

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