5 Replies Latest reply on Mar 11, 2012 10:49 AM by Joe Mako

    Static Rank

    Nathan Schofield

      Hi there,

       

      Is there a way to make a static rank, as opposed to a dynamic one? I want to create a ranking, and then add more dimensions. When I add more dimensions the ranking is ruined.

      See the attached workbook with dummy data. I want to rank a Dimension by a sum of a measure, and then use that rank as static. If you see the second tab I would want the rank to look like (11111,222,333) etc.

      Any help would be greatly appreciated.


      Regards,

        • 1. Re: Static Rank
          Joe Mako

          In this situation, I would recommend using a forumla like:

           

          IF ATTR([Name])=LOOKUP(ATTR([Name]),-1) THEN

            PREVIOUS_VALUE(0)

          ELSE

            PREVIOUS_VALUE(0)+1

          END

           

          from your current view, with a compute using of Table (Down), and then if you need to move the pills, first use the "Show Me" button to lock in this current layout based partitioning.

           

          attached is an example.

          1 of 1 people found this helpful
          • 2. Re: Static Rank
            Nathan Schofield

            Hi Joe,

             

            Thanks for that. That one looks like it works. I have one more ranking question.

             

            I have the situation when I want to refer to ranks in previous periods. In the previous periods I can't get the ranks to stay, they always move as per my current sorting. Is there anyway around this? E.g. see example workbook (here for week 1 I want the rank to be 3,2,1).

             

            Regards,

             

            Nathan

            • 3. Re: Static Rank
              Joe Mako

              Nathan, I believe you can achieve what you are looking for if you can explain it more detail. Can you detail the logic you want to use and the result you expect? currently it is not clear to me.

              • 4. Re: Static Rank
                Nathan Schofield

                Hi Joe,

                 

                Thanks for your response. In the example in the attached workbook -

                 

                     I want a rank for each period. This should rank the names in descending order of dollar amount. I want this rank to be static (so that when I e.g. add dimensions it stays). To date I have only used INDEX() for rank which is dynamic, so does not work for this.

                 

                Does that make sense? Appreciate your input.

                 

                Regards

                • 5. Re: Static Rank
                  Joe Mako

                  I am sorry, that does not make sense to me.

                   

                  Table calculations like Index() are based on the marks in your view, and those marks are based on the dimension pills you have in use, so when you change your pills, the table calculations can change because they are data driven. If you know what dimensions you want to use in the view, you can craft your table calculations to take these changes into consideration and get the results that you want.