Fixate Rank ( IF

[vinieme12]

Hi Guys,

I want to be able to fixate the rank ignoring all selections in ProdName for Brand Field

Currently I have the below table, but the rank changes on selections in the Prodname field even after adding {1} to all aggregations

Current Expression:

=AGGR(rank(sum({1}AGGR(

          if(count(distinct Country#)>3      //sale rows in more than 3 countries

              and

          AGGR(rank(sum({1}Sales),4,1),Brand,ProdName)<=5  // Ranks among Top 5 within the Brand

                    , sum({1}Sales)),

                        Brand,ProdName)),4,1),Brand,ProdName)

Expected Output:

[MK_QSL]

I am not able to get the exact output you have mentioned...

Rank is wrong and Sales is also wrong..

Try my below suggestion and let me know if you think this not work for you..

Create a Pivot Table

Dimension

Brand

ProdName

Expression

Aggr(

IF(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)<=5,

SUM({1}Sales))

,Brand,ProdName)

and

Aggr(

IF(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)<=5,

Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1))

,Brand,ProdName)

[MK_QSL · Accepted solution]

Or use this expressions

IF(Rank(SUM({1}Sales),4,1)<=5,

Aggr(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)

,Brand,ProdName))

and

Aggr(

IF(Rank(SUM({1}Sales),4,1)<=5,

SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales))

,Brand,ProdName)