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

    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