# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Creator II

## FirstSortedValue Function with Aggr

Hello All,

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

Sales:

CountrySales
US2000
China1500
India2500
Japan1000

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

!

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

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.

Some More Example:

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

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

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

Labels (1)
• ### functions

3 Replies
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

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

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

Tags
Community Browser