Modified Competition Rank

Version 4

    Description:

     

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

     

    Example Calculation:

     

     

    IF ISNULL(SUM([Measure])) THEN

      NULL

    ELSE

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

          PREVIOUS_VALUE(NULL),

          WINDOW_COUNT(SUM([Measure])) - RUNNING_COUNT(SUM([Measure])) + 1,

          WINDOW_COUNT(SUM([Measure])))

    END

     

     

     

    Inputs and Setup:

     

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

     

     

    Partitioning and Addressing:

     

    [Dimension or Set being ranked] using an Advanced... sort that is in the opposite direction of the sort that is used for the [Dimension or set being ranked] in the view. For example, a dimension sorted in Descending order in the view would then have this calculation sorted in Ascending order.

     


    Comments:

     

    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, 3, 3, 4.

     

     

    Related Functions/Formulas:

     

    Standard Rank: http://community.tableau.com/docs/DOC-1385

     

    Further Reading/Examples:

     

    Adopted from James Baker: http://community.tableau.com/thread/109042

     

    EDIT: attached a Version 8 twbx