Top N + Nested Sorting in Tableau

0
votes

I have two dimensions(namely state and city) and a measure glid(i need 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 enter image description here

P.S. It can be done by grouping but since there are too many states and cities , it would be very tedious

1
tableau-api

1 Answers

0
votes

I would suggest you to

  • Sort 'State' and 'City' in descending order on Field:'Gild' and Aggregation: Count
  • Create calculated field ranking having 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.

Pls don't forget to let us know if it helped :)