1 2 Previous Next 23 Replies Latest reply on Dec 14, 2012 8:45 AM by Cristina Palo

    Table Calculation: Standard Rank

    James Baker

      Standard Rank



      Alternate names:

      Rank, Rank with Ties, Standard Competition Rank




      This is a ranking function with ties. Tied items share the higher rank.







        IIF(A == LOOKUP(A, -1), PREVIOUS_VALUE(1), INDEX(), 1)



      Compute using: Direction that A is sorted in table, or an "Advanced" field sort on A.



      Inputs and Setup:

      A: An aggregate measure such as "SUM([Measure])", or a calculation producing one.




      IF ISNULL(SUM([Sales])) THEN



        IIF(SUM([Sales]) == LOOKUP(SUM([Sales]), -1),

            PREVIOUS_VALUE(1), INDEX(), 1)



      Compute using: Table




      After a tie occurs and two rows share the same rank, the next row's rank is unaffected - it is the same rank that it would have had if there had been now tie above it. Values of 5, 3, 3, 2 would produce ranks 1, 2, 2, 4.


        • 1. Re: Table Calculation: Standard Rank
          James Baker

          If you for some reason want all the ranks to be negative:



            -IIF(A = LOOKUP(A, -1), -PREVIOUS_VALUE(1), INDEX(), 1)






          Note the need to double-invert the "repeat previous value" case.

          • 2. Re: Table Calculation: Standard Rank
            Richard Leeke

            What does: "an advanced dimensional set ordered by [A]" mean?

            • 3. Re: Table Calculation: Standard Rank
              James Baker

              Hmm, anything you set up using the "Advanced" sub-dialog of the Compute Using, which may not have any visible "direction" on-screen.  Better wording suggestions are welcome.

              • 4. Re: Table Calculation: Standard Rank
                Richard Leeke

                OK, I see what you mean, I thought you must be referring to some whole new concept I hadn't come across yet.


                I agree, it's hard to express this succinctly.  How about something like:


                "Direction that [A] is sorted in the view, or the sort order specified in the Compute Using "Advanced" dialog."

                • 5. Re: Table Calculation: Standard Rank
                  Robb Yeager

                  Very informative Post, but I have hit a snag.  I am trying to do a ranking using a ratio of measures from 2 separate databases.


                  Example.  I am trying to rank Markets based on Calls per User. The calls value is coming from a MySQL Database, the number of users is from an .xls.  I created a calculated field for Calls per user, but that calculated field is not a choice to Sort by.  Unfortunately the data is confidential so I cannot send the book, but am I missing something or is Sorting by Calculated field from Multiple DBs not allowed in 6.0?

                  • 6. Re: Table Calculation: Standard Rank
                    . Jilleneh



                    Thanks for the example.  I'm trying to figure out how to show the rank change over time. If a product is ranked #1 but drops down to #2 the next and back up to #1, I want to be able to show this visually.  I'm attaching a copy of the superstore data to show what I am trying to do.  I'm using the formula for the standard rank and everything has the same rank over time or when I change the way it calculates it, it's not showing me the correct rank.  In the past, I have had to create a set and calculated field for each time period in order to create the rank, but I don't want to have to do that each time.  Is there a way to set up a table calc to have a rank for each time period?



                    • 7. Re: Table Calculation: Standard Rank
                      James Baker

                      Jilleneh, I changed your table calc from:


                      Results are computed along Month, Year of Order Date, Product 2 - Sub-Category (sorted descending by Sum of Sales).




                      Results are computed along Product 2 - Sub-Category (sorted descending by Sum of Sales) for each Month, Year of Order Date.  Relative LOOKUP values are fetched at the Product 2 - Sub-Category level.


                      And that seemed to do the trick.


                      To be clear, that's Compute using: Advanced - Product 2 then MonthYear (sorted by Sales desc), At the level: Product 2, Restarting every: MonthYear

                      • 8. Re: Table Calculation: Standard Rank
                        Joe Mako



                        I don't understand your partitioning instructions, attached is what I came up with. In the attached image, I have the following:


                        For Compute using, Advanced:

                        - Month, Year of Order Date

                        - Product 2 - Sub-category


                        Order along: Sales SUM Descending


                        Restarting every: Month, Year of Order Date


                        Because within each Month we want to sort the Product Category Descending by the Sum of Sales.


                        My description reads:

                        "Results are computed along Product 2 - Sub-Category (sorted descending by Sum of Sales) for each Month, Year of Order Date."


                        Also because there were NULL values for some product-Month combinations, I changed the formula so it would not rank null values as 1


                        Here is the modified function:



                        IF NOT ISNULL(SUM([Sales])) THEN
                           IIF(SUM([Sales]) == LOOKUP(SUM([Sales]), -1),
                              PREVIOUS_VALUE(1), INDEX(), 0)


                        This way both Unknown and NULL values for SUM([Sales]) are returned with NULL


                        Also, when visualizing rank like this, it makes sense to reverse the axis for the Rank field, the 1st or one with best value is charted at the top.


                        I attached a workbook of this in action, with null marks formatted to hide and break lines, along with another sheet that shows another way to look at ranking over time.

                        • 9. Re: Table Calculation: Standard Rank
                          Joe Mako



                          Unfortunately, the field that you would want to use for the Order Along option would be something like:


                          Calls Per User


                          SUM([Calls])/SUM([Market Users (Market Calls Users.xls)].[Users])


                          I do not believe it is currently possible to use a formula based on either a data blending calculation or a custom table calculation as the field to define the Order Along option with the Advanced Calculate within dialog.


                          I would recommend you start a new thread in the "Wouldn't it be nice" section of this forum as a feature request with your example use case.

                          • 10. Re: Table Calculation: Standard Rank
                            Robb Yeager

                            Thanks for the feedback Joe!  I got it to work by holing down CTRL and selecting the "Calls per User" and "Market" , then hitting the sort button at the top.  Problem is it just creates a manual sort,so every month I have to download the workbook from Tableau Server, Re-Sort, then re-publish.


                            I will Start a new thread in "Wouldn't it be nice" today about this.


                            Thanks again!

                            • 11. Re: Table Calculation: Standard Rank
                              guest contributor

                              James, Thanks for posting this. I have a quick question that relates to applying this function to my own dataset.


                              I've managed to do the dynamic quarterly rank similar to what joemako was doing. The challenge comes about when an entity may have a value in later quarters (and thus get picked up in the sum[sales] formula), but if the product was not sold in earlier quarters its rank would be returned as 1.


                              So, how can I get items with no entries in each relevant quarter to return null, whilst leaving the winner with a '1' ranking?


                              Thanks for your help

                              • 12. Re: Table Calculation: Standard Rank
                                guest contributor

                                The other thing I'd like to be able to do is to create a dynamic group/set of the top 10 in each period (year, quarter, or month), and another group of those ranked 11-20, and those 21-30. These groups would be dynamic in the sense that a party may be in one set in one period, and another set in the next period. Set membership wouldn't matter, only that aggregate of that group.


                                This would allow us to see the contribution of the top 10 parties/products to overall sales, and the contribution of the next 10, and the next 10. To round it off, a final group/set would have all the other (non-member of top 1-30) parties.


                                Any idea how this can be done?

                                • 13. Re: Table Calculation: Standard Rank
                                  Joe Mako



                                  This is a situation like the one above where you need a modified calculation, something like:



                                  IF NOT ISNULL(SUM([Installed Capacity])) THEN
                                   IIF(SUM([Installed Capacity]) == LOOKUP(SUM([Installed Capacity]), -1),
                                  PREVIOUS_VALUE(1), INDEX(), 1)


                                  with the partitioning set like the attached dialog image.


                                  Then you can create and place a function like:



                                  IF [Rank] > 10 THEN "> 10"
                                   ELSEIF [Rank] > 0 THEN "1 - 10"


                                  for grouping, it should inherit the partitioning already set. This will create a group of nulls as well, you can either hide or filter them out.


                                  Both these things are done in the attached workbook.

                                  • 14. Re: Table Calculation: Standard Rank
                                    guest contributor

                                    I'm getting a bit worried.  Is the above just a workaround because the datasource does not handle the rank() function?  I really hope so because I have the rank column but am unable to put it in this format:


                                    company ABC

                                    ITEMS    2009    2010    rank(among the industry)\

                                    chairs  1K      2K      2

                                    tables  2K      3K      1


                                    My sql result set looks like this:


                                    company    year  chairs  chair-rank tables tables-rank

                                    abc        2009    1K        5      2K    2

                                    abc        2010    2K        2      3K    1

                                    def        2009    3K        1      5K    1

                                    def        2010    4K        1      3K    2


                                    To make matters worse I will only be using one ranking (of the current year).  Any ideas?  Do I need to change my SQL to accomadate the above tabular format cuz right now i'm having helluva time trying to get it right.

                                    1 2 Previous Next