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,240 points
546 views

1 answer to this question.

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,530 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,240 points
1,372 views
+1 vote
2 answers

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

You absolutely can join to a calculated ...READ MORE

answered May 29 in Tableau by anonymous
1,685 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
1,926 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
• 31,840 points
718 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,240 points
2,253 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,240 points
103 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
854 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,530 points
83 views