4 Replies Latest reply on Jun 18, 2012 2:13 PM by Alex Kerin

# Show Top 5 for each parent

Sorry if this has already been answered but I can't seem to find a solution.  I am trying to get the top 5 items for each department.  My final report would look like:

Department 1

Item D     \$\$

Item C     \$\$

Item G     \$\$

Item A     \$\$

Item M     \$\$

Department 2

Item 64     \$\$

Item 43     \$\$

Item 3       \$\$

Item 29     \$\$

Item 17     \$\$

And so forth.  The problem is when I select Top 5 items it only gives me the top 5 items in total not for each department  I want the top 5 itmes for each department.

Thanks in advance for any help.

-Andrew

• ###### 1. Re: Show Top 5 for each parent

One way to do it: we need to check for the top 5 in a different way for each state. Here we use a calculation looking at index()<6 to hide any items lower on the list. This is set to ignore sub-category and look across the state:

Hang on, my sort is wrong - we need to sort using a set. Back soon with proper answer.

• ###### 2. Re: Show Top 5 for each parent

Hey Andrew,

I have found http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category to be extremely helpful in doing exactly what you are looking for.  You might have to play with it a bit to get it perfect, but follow that guide and you should be able to get the top 5 items per department.

--Jeff

• ###### 3. Re: Show Top 5 for each parent

Jeff's method has the advantage of explicitly setting the sort whereas mine relies on you doing it from the pill (e.g. you can get bottom 5 by just changing the sort order on mine)

Edit: looking at that method, you could explicitly set it. They actually don't, relying on sorting via the pill menu.

Edit 2: Ok, so this KB was probably created before the introduction of index(). However, it does get right what I didn't - that is the proper nested sort.

1 of 1 people found this helpful
• ###### 4. Re: Show Top 5 for each parent

Okay, now we use a nested sort using a set of state and sub-category. Then use the index function again with a sort built in to it - so it is now explicitly set.