Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

FirstSortedValue Function with Aggr

Hello All,

Using FirstSortedValue and Aggr functions we can found the specific string from our data set.

Let's start with a simple example.



Now I want to found which country has highest sales? For this, We can use FirstSortedValue with Aggr function.



Let's understand the expression: 

  1. Aggr(Sum(Sales), Country): Finding Sum of Sales aggregated by Country.
  2. FirstSortedValue([Country], -aggr(Sum9Sales),[Country]),1): Now We need the country name based on higest sales. So, We've used Country field in FirstSortedValue function.
    • Minus(-) Sign: if we did not put minus sign then upper expression give the lowest sales country name.
    • Number (rank): put your desired number rank in the last.

So Expression gives the answer: Canada

Some More Example:

1. FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),2)

           Answer : India

2. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),1)  //Remove Minus sign

          Answer: Japan

3. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),2)  //Remove Minus sign

          Answer: China

3 Replies
Contributor III
Contributor III

it possible to return the actual value as well as the Country? 

Once i know the country with the top sales i want to know the value of that as well.

Contributor III
Contributor III

IN this example you dont need to do the Aggr inside the FirstSortedValue function as we have single sales value for a country for this example even if we pass sales dimension instead of Aggr function it will give us the correct result as desired.

You would require the Aggr function when we are having multiples vales of sales for a single country and then you wanted to do the group by Sales  Country Wise, as this will give me single sum value for each country.

Hope this clear the things more 

Creator II
Creator II


Just use Max([ValueField]) or if you have multiple values MAX(AGGR(SUM([ValueField]), [GroupField])).