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 4, 2018 in Tableau by Atul
• 10,240 points
4,263 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 4, 2018 by ffdfd
• 5,550 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 25, 2018 in Tableau by Atul
• 10,240 points
7,611 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 30, 2019 in Tableau by anonymous
24,466 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
15,296 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, 2019 in Tableau by Cherukuri
• 33,030 points
10,637 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
9,832 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
1,561 views
+2 votes
1 answer
0 votes
1 answer

Reset filters in Tableau

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

answered Mar 30, 2018 in Tableau by QueenBee
• 1,820 points
3,993 views
+2 votes
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 ffdfd
• 5,550 points
861 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP