topic Re: How to use Rank in a KPI? in App Development

How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 16:20:46 GMT

Hi,

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

Show a quantity for a commodity that has been flagged as "aged" (Flag_Aged = 1)
Limit the quantity shown, so it's only for the specified rank (vRank = 1 for example)
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.

Re: How to use Rank in a KPI?

MatheusC — Mon, 25 Nov 2024 16:57:08 GMT

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

Re: How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 17:17:43 GMT

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.

Re: How to use Rank in a KPI?

MatheusC — Mon, 25 Nov 2024 17:36:53 GMT

My mistake, that's all I missed =

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

Re: How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 17:38:47 GMT

Perfect. Thank you.

Re: How to use Rank in a KPI?

brunobertels — Mon, 25 Nov 2024 17:39:35 GMT

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

)

Re: How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 17:39:46 GMT

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

Re: How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 18:27:12 GMT

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.

Re: How to use Rank in a KPI?

MatheusC — Mon, 25 Nov 2024 18:35:42 GMT

for the label, see like this:

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

Re: How to use Rank in a KPI?

mikegrattan — Mon, 25 Nov 2024 18:44:22 GMT

Hi Matheus,

That worked great. Thank you very much.