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

    Table Calculation: Standard Rank

    James Baker
      Name:

      Standard Rank

       



       

      Alternate names:

      Rank, Rank with Ties, Standard Competition Rank

       



       

      Summary:

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

       



       

      Definition:



      IF ISNULL(A) THEN

        NULL

      ELSE

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

      END

       

      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.

       



       

      Example:



      IF ISNULL(SUM([Sales])) THEN

        NULL

      ELSE

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

            PREVIOUS_VALUE(1), INDEX(), 1)

      END

       

      Compute using: Table

       



       

      Commentary:

      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:

           



          IF NOT ISNULL(A) THEN

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

          ELSE

            NULL

          END

           



           

          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

                    Hi

                     

                    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?

                     

                    Jillene 

                    • 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).

                       

                      to:

                       

                      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

                        James,

                         

                        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)
                        END
                        


                         

                        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

                          Robb,

                           

                          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

                                  sunwiz,

                                   

                                  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)
                                  END
                                  


                                   

                                  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"
                                  END
                                  


                                   

                                  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