Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Search instead 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.

Let's start with a simple example.

Sales:

CountrySales
US2000
China1500
India2500
Japan1000
Canada4000

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.

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

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 II

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

Tags
Community Browser