7 Replies Latest reply: Jan 28, 2014 12:17 PM by Bérengère BOILEAU

# Excluding "other data" in a rank

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

• ###### Re: Excluding "other data" in a rank

Do something like below trick..

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

• ###### Re: Excluding "other data" in a rank

Or...

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

• ###### Re: Excluding "other data" in a rank

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

• ###### Re: Excluding "other data" in a rank

Something like below....

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

• ###### Re: Excluding "other data" in a rank

Thank you for your help Manish !

I posted below exact response.

Regards

Bérengère

• ###### Re: Excluding "other data" in a rank

Maybe try this:

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

Hope this helps!

• ###### Re: Excluding "other data" in a rank

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