2 Replies Latest reply: Mar 16, 2015 10:57 PM by Francois Tremblay

# Problem with my  'firstsortedvalue' expression, Thanks!

Hello everyone!

I succeeded with Highest and lowest price to get the country name and MonthYear, but using the same expression, I can't get it for for my highest export in \$ as show in the orange box below the 3,576,515 \$ :

Here's my expressions for the Country:

firstsortedvalue (country, -Aggr(Max[ExpVal],country))

// ExpVal = Export Value in US\$

And my expressions for the MonthYear (MM-YYYY):

firstsortedvalue (MonthYear, -Aggr(Max[ExpVal],MonthYear))

// ExpVal = Export Value in US\$

I also tried with the expression:

only(if(aggr(Rank(Sum([Sales Amt])),Salesperson)=1,Salesperson))

But I get the same empty result.

Thanks for you collaboration!

• ###### Re: Problem with my  'firstsortedvalue' expression, Thanks!

Hi,

I would use:

=firstsortedvalue (country, -Aggr(sum[ExpVal],country))

=firstsortedvalue (MonthYear, -Aggr(sum[ExpVal],country))

If this still returns null ,it may be the case that you have more than one first value. You can use this solution adapted from Clever Anjos :

=FirstSortedValue(country,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

=FirstSortedValue(MonthYear,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

This will use the alphabetical order (of the country name) to be your second sort.

I hope this helps.

Kind regards,

Nuno

• ###### Re: Problem with my  'firstsortedvalue' expression, Thanks!

Thanks Nuno!

The second expression type

=FirstSortedValue(country,-Aggr(Sum([ExpVal])+rank(country,country)/10,country))

is working just fine.

Thanks again Nuno!