8 Replies Latest reply: Nov 22, 2017 11:21 AM by omar bensalem RSS

    Rank within group

    Lucas Westmaas

      Hi,

       

      I'm trying to produce a table that shows ranks within group. Take the data below:

       

      OfficeGroupScore
      1A5
      2A9
      3A1
      4B10
      5B19
      6B5
      7B3
      8B4

       

      I haven't been able to produce rank within group. For example, the rank for Office 2 should be 1 because they are the top office in Group A even though there are two offices in Group B with a better score.

       

      I need to be able to do this even if Group is not part of the visualization, if that makes a difference.

       

      I'm guessing the solution requires AGGR but I haven't been able to get it to work.

       

      Can anyone help? Thanks in advance!

       

      Lucas

        • Re: Rank within group
          Andrey Khoronenko

          Hi,

           

          May be simple table wih

           

          Dimehsion: Office

           

          Exprssion: Rank(Sum(Score), 4)

           

          Regards,

          Andrey

          • Re: Rank within group
            Beck Bakytbek

            Hi Lukas,

             

            try it:

             

            Aggr(Rank(Sum({1}Score)),Office) or Aggr(Rank(Sum({1}Score)),Group)

             

             

             

            beck

              • Re: Rank within group
                omar bensalem

                No Beck, that won't achieve what's he's looking for:

                 

                this will do it:

                Aggr(Rank(Sum(Score)),Group,Office)

                 

                result:

                Capture.PNG

                  • Re: Rank within group
                    Lucas Westmaas

                    Omar - thanks, that worked! I swear I tried that yesterday but apparently not. I'm having a related issue now - maybe you can help? This formula correctly assigns rank to all selections but does not assign a value to non-selected values. This is important because my visualization basically constructs comparison groups based on the selection - e.g. if you select Office 1, then the data for Offices 2 and 3 (the other group A offices) will also appear for context. Your formula correctly assigns rank 2 to Office 1 when Office 1 is selected, but does not show rank for Offices 2 or 3. Set analyses don't appear to make a difference here.

                     

                    I've tried all three of:

                    aggr({<1>} rank(sum(Score)),Group,Office)

                    aggr(rank(sum({<1>} Score)),Group,Office)

                    aggr({<1>} rank(sum({<1>} Score)),Group,Office)


                    When I just use rank(sum({<1>} Score)) I am getting values to appear, so it seems to be something to do with AGGR even though I am specifying to ignore selections there. Any ideas?

                    • Re: Rank within group
                      Tatiana Kozel

                      Dear Omar,

                      Maybe you can help me on this.

                      It's some another from guys problem

                      So, I have this table

                         

                      CountryRegionalSum
                      Aa1123
                      Aa2140
                      Aa3141
                      Aa4144
                      Bb1110
                      Bb22
                      Bb35
                      Cc120
                      Cc2114

                       

                      And shoud show only  the the list of countries  and only 1 region sales  in each country with first  rank in this country.

                      it should look like this

                         

                      CountryRegionalSum
                      Aa4144
                      Bb1110
                      Cc120

                      or

                        

                      CountrySum
                      A144
                      B110
                      C20

                       

                      I hope you can help on this. This principle is very useful, when you have a lot of regions, periods and sums and you should show only top of performance.

                  • Re: Rank within group
                    kushal chawda

                    try this expression

                     

                    =rank(sum(total <Office,Group>Score),0,1)