Excluding "other data" in a rank

[#1]

Hello everybody,

I have a table with companies and their sales. I want to display only first 30 values and group all other companies in a line “Other companies”. For this, I checked the box “Display other” in dimension limits.

I also want to display rank of each company (based on sales). For this, I use the following :

=Rank(Sum({$<
$(CTXT_MARKET),
$(CTXT_MIDAS),
$(_MAT)
>}
Sales))

My problem is I want to display the rank of each company without displaying the rank for the line “Other companies” and without that this line influence ranking of other companies.

I would like :

Rank              Company                      Sales

1                    Toto                                 300$

2                    Titi                                   200$

3                    Tutu                                 100$

...                    ...                                      ...

-                    "Other companies"              500$

But I have  :

Rank              Company                 Sales

2              Toto                             300$

3              Titi                               200$

4              Tutu                             100$

…             …                                  …

1              “Other companies”          500$

Even if sum of sales for “other companies” is more important, I don’t want that this line has the rank number 1 because it doesn’t represent a single company but a sum of other companies.

Do you know how can I “exclude” this line in formula to have true ranking ?

Thank you a lot

Regards

Bérengère

[MK_QSL]

Do something like below trick..

=Rank(Sum({$<
$(CTXT_MARKET),
$(CTXT_MIDAS),
$(_MAT)
>}
Sales))-1

[MK_QSL]

Or...

=IF(AGGR(Rank(Sum({$<
$(CTXT_MARKET),
$(CTXT_MIDAS),
$(_MAT)
>}
Sales)),COMPANY)<=30, Rank(Sum({$<
$(CTXT_MARKET),
$(CTXT_MIDAS),
$(_MAT)
>}
Sales))-1)

[#4]

Thank you Manish for your response.

Your proposition works if I'm sure that "Other companies" have the most important sales. But if next month, a single company, for example, "Toto", has more sales than "other companies", rank will be false...

Regards

Bérengère

[MK_QSL]

Something like below....

=Rank(SUM($<$(CTXT_MARKET),$(CTXT_MIDAS),$(_MAT), Company = {“=Rank(Sum({$<$(CTXT_MARKET),$(CTXT_MIDAS),$(_MAT)>} Sales))”}>}Sales))

[jerem1234 · Accepted solution]

Maybe try this:

=Rank(if(Count(distinct Company)=1,Sum({$<
$(CTXT_MARKET),
$(CTXT_MIDAS),
$(_MAT)
>}
Sales)))

Hope this helps!

[#7]

Thank you jerem1234 !

Your response was very useful! In fact, expression is :

=Rank(if(count({<$(CTXT_MARKET),

    $(CTXT_MIDAS),

    $(_MAT)>}Company)=1

,Sum({$<

$(CTXT_MARKET),

$(CTXT_MIDAS),

$(_MAT)

>}

Sales)))

Regards

Bérengère

[#8]

Thank you for your help Manish !

I posted below exact response.

Regards

Bérengère