4 Replies Latest reply on May 10, 2016 6:16 AM by Thomas Beaulieu

# Help with Rank() function

Hello - referring to the attached workbook, I am trying to get the Rank() function to show a different value for each item depicted on the table but no matter what I try I cannot get ranks 1, 2 and 3 for item ABC, PQR and XYZ respectively.  I either get what is shown or I get a rank value for each individual line (ranks 1 through 17).  Any help would be much appreciated.

I should point out that I have data for the years 2014 and 2015 hidden from view.  I would like the rank based on the sum of price in descending order for all three years combined even though I am only displaying 2016 data.

Thanks,

Tom B.

• ###### 1. Re: Help with Rank() function

Hi Thomas,

If I am understanding your requirements correctly, this might work for you. You need to go to "Edit Table Calculation":

Under Compute Using, select "Advanced." You'll need to address item, bill name and order number (in that order):

Finally, for "At the level:" select Item:

Gives you the following output:

Let me know if this helps or if I've misunderstood your requirements.

Thanks,

Ben

1 of 1 people found this helpful
• ###### 2. Re: Help with Rank() function

Thomas,

First Step- To get one rank per Item

right-click rank, Edit Table Calculation, compute using  Advanced, bring  Item, Bill Name and Order number in Addressing.

Compute at the level Item.

Next - To get the sorting

Right-click Item , select Sort , Sort By Field, select Price - Aggregation Sum

Finaly - To keep the sorting based on all the data and not only the data in the view, you need to filter the year using a table calculation.This will force Tableau to compute the sorting before filtering the date.

See in the attach (you will need to install a copy of 9.3 to open the workbook, two versions can be installed on the same PC)

Michel

• ###### 3. Re: Help with Rank() function

Awesome job Ben, thanks!

Tom B.

• ###### 4. Re: Help with Rank() function

Hi Michel and thanks.  I'll check this out when I get 9.3 installed.

Tom B.