Qlikview: How to find a dimension value which corresponds to MAX or MIN of an expression

Scenario:
We need to find the countries who have the MAX and MIN sales and store them in variables to use in  set analysis.




Solution:
FIRSTSORTEDVALUE function can sort the fields according to a sort-weight (another field or an aggregation) and return the corresponding value of the field.


In our case we need to sort the countries by their total sales, thus we will use AGGR(sum(sales),country) as our sort-weight in FIRSTSORTEDVALUE function:

vCountryWithMaxSales =FirstSortedValue(Country, -Aggr(sum(Sales),Country))


The above expression returns the country which has the MAX sales. 


By default, the FIRSTSORTEDVALUE function sorts in ascending order. For sorting in descending order, we use the minus sign in front of the sort-weight. 


So, if we would like to get the country which has the MIN sales, we can just remove the minus sign:

vCountryWithMinSales =FirstSortedValue(Country, Aggr(sum(Sales),Country))

Warning:

If more than one country have the same MAX or MIN sales, then the FIRSTSORTEDVALUE function will return NULL. To avoid this, we can use DISTINCT qualifier:

vCountryWithMaxSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))
vCountryWithMinSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))



Sample:
https://sites.google.com/site/quickdevtips/home/findmax.qvw?attredirects=0&d=1