4 Replies Latest reply on Jul 13, 2016 5:07 PM by swaroop.gantela

# Multiple ranks for hierarchical categories

I'm hoping someone can help me out; I've been struggling a lot over the last week trying to find an answer in the forums that doesn't go beyond my beginner/intermediate Tableau knowledge.

My revenue data is split into three levels, examples below:

• Continent (e.g. North America, South America, Europe) >
• Country (e.g. UK, Spain, Canada, France, Italy) >
• Product (e.g. Car, Bike, Plane, Scooter, Unicycle).
• \$ Revenue

For each of the regions, I want to

1. Rank and filter the top 3 Countries (by their revenue sub-total)

then

2. Rank and filter the top 3 products by revenue for each of the top three ranked/filtered countries (and still show the revenue per product).

I need to have Quick filters for both ranks, so I can make this analysis available within my org.

This doesn't feel like it should be overly complicated, but I'm completely stumped. If anyone can provide step by step instructions, I would be eternally grateful.

• ###### 1. Re: Multiple ranks for hierarchical categories

Dan,

I'm not sure if I quite got there, but maybe the attached could be a first step.

I made a worksheet with Continent, Country, and Product.

I created a Rank of the Product Revenue with:

RANK(SUM([Revenue]),'desc')

and edited the Table Calculation to have Partitions of Continent, Country, Product,

At the deepest level, restarting every Country.

I also sorted the Products by descending Sum(Revenue).

I then used a Level of Detail calculation to get the Total Country Revenue:

{ FIXED [Country]:SUM([Revenue])}

I sorted the Countries by descending [CountryRevenue].

I created a calculation of simply INDEX() and placed that on the worksheet.

I edited the Table Calculation to have Partitions of Continent, Country, Product,

At the of [Country], restarting every [Continent].

I was then able to put both [ProductRank] and [Index]

on the filter shelf and just select choice 1, 2, and 3.

• ###### 2. Re: Multiple ranks for hierarchical categories

Hey I have the same kind of question.

I have a Main Application: XYZ under this Main Application I have multiple Application ID say A,B,C,D,E,F. Now under these Application IDs I have multiple Function IDs say  App ID A has 1,2,3,4,5,6,7: App ID B has 7,8,9,0 and so on.

Now what I need is Rank the Application ID and Rank the Function IDs under those Application IDs by a count if usernames. Till now what I have achieved is only the ranking of Function IDs.

Desperatelty need help!!

I tried your method but it isn't working.

• ###### 3. Re: Multiple ranks for hierarchical categories

Hi Dan,

Regards,

• ###### 4. Re: Multiple ranks for hierarchical categories

Kriti,

Please see if the attached could be a first step for you.

First, I calculated counts of UserNames at each level (Function,App,Main)

using Level of Detail calcuations. For example [CountFunctionUsers] is :

{ FIXED [Main App],[App ID],[Function ID]:COUNT([Username])}

and [CountAppUsers] is:

Then used the technique described here to make combined fields:

Nested Sorting | Tableau Software

For example, I combined AppID and FunctionID

and put that on Rows shelf of Sheet2. I then sorted that by

descending and SUM(CountFunctionUser).

Likewise, this can be done for Main and App.

The Combined field can be hidden from the view