Get last rank for each country

[sergio0592]

Hi all,

I had a reflection on a topic since yesterday. For each country i must get line for the first rank of sales and for the last rank of sales. I manage easily to get the first rank line with if (rank=1...) but i'am unable to get last rank line .

In the sample below, i'd like use in my formula if(rank=7...   if(rank=6....   if(rank=5... but don't work with max(rank(Sales))

Expected:

Regards

[beck_bakytbek]

Hi Jean,

do you want to see only last 3 values with rank : 7,6,5 ?

[sergio0592]

Yes.

[kaushiknsolanki]

Hi,

Try firstsortedvalue() function like below.

Create the straight table and keep Country and Seller_Id as dimension

=Sum({<Seller_Id = {"$(=Concat(Distinct Aggr(FirstSortedValue(Total < Country>Seller_Id ,-Sales),Country),'","'))"}>}Sales)

Regards,

Kaushik Solanki

[MK_QSL]

Create a Straight / Pivot Table

Dimension

1) Country

2) =Aggr(IF(Rank(SUM(Sales))=Count(Total <Country> DISTINCT Seller_ID),Seller_ID),Country,Seller_ID)

Tick Suppress When Value is Null for this Calculated Dimension

Expression

1) SUM(Sales)

2) Aggr(Rank(Sum({<Country>}Sales),1,1), Country, Seller_ID)

[antoniotiman]

Hi,

Dimension : Country,Seller_Id

Expression :

If(Sales=RangeMin(Top(Sales,1,NoOfRows(TOTAL))),Sales)

And/or

If(Sales=RangeMin(Top(Sales,1,NoOfRows(TOTAL))),Rank(Sales))

Regards,

Antonio

[beck_bakytbek]

Hi Jean,

see attached example

I hope that helps

[swuehl]

If

=If( Rank(Sum(Sales))=1, ....)

works to retrieve the max sales per country, you just need to negate the sum(Sales) to get the min sales per country:

=If( Rank( -Sum(Sales))=1, ....)

[prachisangewar]

hi,

You can get the results using the below formula:

=If(Aggr(Rank(Aggr(Sum(Sales), Country, Seller_ID), 3, 1), Country,Seller_ID) = 1 OR  Aggr(Rank(Aggr(Sum(Sales)*-1, Country, Seller_ID, 3, 1), Country,Seller_ID) = 1,

Aggr(Rank(Aggr(Sum(Sales), Country, Seller_ID), 3, 1), Country,Seller_ID)

)

[sergio0592]

Thanks to all for your answers. Learn so much with all of you!!!