How to use Rank in a KPI?

[mikegrattan]

Hi,

I'm trying to do a few things in a KPI:

1. Show a quantity for a commodity that has been flagged as "aged" (Flag_Aged = 1)
2. Limit the quantity shown, so it's only for the specified rank (vRank = 1 for example)
3. Show the name of the commodity in the label

This expression works for ranking the commodity by the amount that is flagged as "Aged" - it is the definition of the variable "vRank":

rank(Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity))

The expression the KPI is:  Sum({<vRank={1},Cooler={'SAL','YUM'},Flag_Aged={1}>} Quantity)

However, the result is not being limited to the Commodity that is ranked as 1. 

Please advise. Thank you.

Screenshot shows KPI result and table of top 10 commodities, using the Fixed Number limitation on Commodity. The expression for the Quantity Aged field is: Sum({<Flag_Aged={1},Cooler={'SAL','YUM'}>}Quantity). In this example, I'm trying to get the KPI to show the Quantity Aged for commodity ROM since it is ranked #1.

[MatheusC]

Hi, 

Maybe this, inserting the rank in your set analysis:

sum({<Commodity={"rank(Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity))=1"}>}Quantity)


- Regards, Matheus

[mikegrattan]

Hi Matheus,

I'm getting a 0 as the result of that expression. I updated the data load script to exclude various Coolers and Commodities, so the set expression can now be simplified to 

sum({<Commodity={"rank(Sum({<Flag_Aged={1}>}Quantity))=1"}>}Quantity).

I also tried 

Sum({<Commodity={"rank(Sum({<Flag_Aged={1}>}Quantity))=1"},Flag_Aged={1}>}Quantity)

but that results in a 0 value as well.

[MatheusC · Accepted solution]

My mistake, that's all I missed =

sum({<Commodity={"=rank(Sum({<Flag_Aged={1}>}Quantity))=1"}>}Quantity)

[mikegrattan]

Perfect. Thank you.

[brunobertels]

Hi 

Not Sure but you can't call a variable (vRank) in a set analysis : it must be a dimension 

So try 

IF(

rank(Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity)) = 1 , 

Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity), 0

)

and may be to get the commodities : 

firstsortedvalue (

commodities , 

IF(

rank(Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity)) = 1 , 

Sum({<Cooler={'SAL','YUM'},Flag_Aged={1}>}Quantity), 0

)

)

[mikegrattan]

Oh, almost forgot. How do I get the label of the KPI to show the Commodity?

[mikegrattan]

Hi Bruno,

I tried your expression using FirstSortedValue, but it's throwing an error about a nested aggregation. I tried searching for that error and found several posts in Qlik Community with no solution. Not sure what to do at this point.

[MatheusC]

for the label, see like this:

=only({<Commodity= {"=Rank(sum({<Flag_Aged={1}>}Quantity))=1"}>} Commodity)

[mikegrattan]

Hi Matheus,

That worked great. Thank you very much.