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

Qlikview: Aggregation 3: number of MAXIMUMs in a dimension field

Scenario:
We have a data-set containing the market price of some commodities. And we need to find:

1. The months in which the maximum market price of each commodity is reached
2. How many commodities have reached their maximum market price in each month? (e.g. if only commodity A and commodity C have reached their maximum market price in April, then it is 2 for April)

Solution:

1. Finding the maximum price for each commodity is easy, we will just create a straight table with COMMODITY field as dimension and MAX (PRICE) as expression. However, our aim is to find the months in which the maximum market price of each commodity is reached. So we will add a second expression as below:
            =MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth))
      
2. The solution of item 1 will lead us to a solution for item 2. We just need the count of the COMMODITYs which are associated with the MONTHs returned by the second expression used in the solution of item 1.  So, if we use the AGGR function as follows, we can count the COMMODITYs:
   
   dimension:  =aggr(MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth)),Commodity)
   
   expression:  =count (Distinct Commodity)

Here is the implemented solution:

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