How to perform top N fields with nested sort in tableau

+1 vote

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 

enter image description here

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?

Aug 3, 2018 in Tableau by Atul
• 10,190 points
95 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

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

answered Aug 3, 2018 by Atul
• 5,470 points

Related Questions In Tableau

0 votes
1 answer

How to combine rows with same information in Tableau

@Ghost, Tableau has a function RANK_UNIQUE which you ...READ MORE

answered May 24, 2018 in Tableau by Atul
• 10,190 points
349 views
+1 vote
1 answer

How to join tables based on “Calculated Fields” in Tableau?

Calculated fields are not written back to ...READ MORE

answered Aug 23, 2018 in Tableau by Naruto
• 710 points
475 views
0 votes
1 answer

How to get multiple Sets of Measure Names with different Filters on a single sheet in Tableau?

The easiest solution to your query is: 1) ...READ MORE

answered Aug 23, 2018 in Tableau by Naruto
• 710 points
557 views
0 votes
1 answer

How to sort by any measure in a Tableau table?

Hi Sindhu, Once you have created a bar ...READ MORE

answered Mar 8 in Tableau by CHERUKURI
• 12,980 points
63 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,190 points
906 views
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,190 points
36 views
+2 votes
1 answer
0 votes
1 answer

Reset filters in Tableau

I understood your question (I hope so!). ...READ MORE

answered Mar 29, 2018 in Tableau by QueenBee
• 1,810 points
281 views
+1 vote
1 answer
0 votes
1 answer

How to get Tableau results in python?

Well even I was looking for the ...READ MORE

answered Apr 9, 2018 in Tableau by Atul
• 5,470 points
38 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.