How to perform top N fields with nested sort in tableau

Question

What I have?

I have two dimensions(state and city) and a measure (glid). I want to find the count of glid since glid is a unique reference no.

I want to arrange my data such that for each state I get only the top 3 or n cities having the highest count of glids, and I want the rest of the cities to be clubbed under a separate category, say "others".

I have done my due research but since I am new to tableau, could not fathom it.

Please be more elaborate in answering. I am attaching an image for further help 

I know it can be done by grouping but since there are too many states and cities, it would be very tedious, can you suggest something?

Answer 1

You can use the following steps:

• Sort 'State' and 'City' in descending order on Field:'Gild' and Aggregation: Count
• Create calculated field ranking having the definition as index()
• Drag it to 'Rows'. Right-click this field in here and select 'Edit table calculation'. Select 'Compute Using': Specific Dimensions, Below that check 'State' & 'City' (in order), 'At the level':Deepest, 'Restarting Every': State and other default selections.
• Create parameter Top 3 - Data type: Integer; Current value: 3; Display format: Automatic; Allowable values: Range; Minimum:3; Maximum:10; Step size:1
• Right click this parameter and select 'Show parameter control'
• Create calculated field Top_3_calc

IF  [ranking] <= [Top 3] THEN "Top Values"

ELSE "Others"

END

•  Drag this calculated field to 'Rows'. Right-click this field in here and select 'Edit table calculation'. Select 'Compute Using': Specific Dimensions, Below that check 'State' & 'City' (in order), 'At the level': Deepest, 'Restarting Every': State and other default selections.

After this, you would require to do few cosmetic changes in order to achieve your specific requirement.

Interested to learn about Tableau from top experts? Enroll in Tableau Course to get expert guidance.