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

# Table Calculation: Standard Rank

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  NULLELSE  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  NULLELSE  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

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  NULLEND `

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

• ###### 2. Re: Table Calculation: Standard Rank

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

• ###### 3. Re: Table Calculation: Standard Rank

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

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

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

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

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

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.

"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

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

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

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

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

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

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