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.

Let me know if it worked for you