5 Replies Latest reply on Oct 9, 2013 1:21 PM by Julia Hennelly

# How to set up a Top N with a calculated rolling 3 month percentage

I thought this would be really straightforward but it does not seem to be so and hence I am guessing I am missing something obvious.  I have a list of departments with their turnover percentages (based on a rolling 3 month headcount).  I need to at least be able to sort descending with the department with the highest turnover at the top.  What I really want to do is show a Top N.  I am attaching my workbook which is very simple.  I thought that if the user clicked on the sort button next to turnover percent they would see the departments sorted in descending order by turnover. I also do not see the calculated field as a sort option?

Any ideas?

Julia

• ###### 1. Re: How to set up a Top N with a calculated rolling 3 month percentage

See the attached for a solution to both problems Julia.

The issue of the sort is solved by the Turnover % Sort 3 Month calculation. You do not see the field in the sort options becuase it is a table calculation. To sort with these, you just have to put a discrete copy of the filed on the rows shelf. Finally, click Format, click Borders and raise the Row Divider to the highest level. This prevents us from drawing dividers based on the Turnover % value. If you move this to the middle level, you will now see that Administration and Financial Services are shown in the same pane since their values are the same. This makes the viz look messy.

Now that we are sorted, we can just run a typical top n calc without having to worry about sorting it. index()<[Top N] is the formula of the Top N Calc field. Since this is set to table down, we will just keep the Top N based on Turnover % since we already have our view sorted.

Hope this helps,

Dan

• ###### 2. Re: How to set up a Top N with a calculated rolling 3 month percentage

Neat trick.  I did not see this part in the original description:  Once the discrete (blue pill) copy of "Turnover % Sort 3 Month" is added as the first pill on the Rows shelf, you must deselect the  "Show Header" option for that pill so that the values are not shown in the view.

• ###### 3. Re: How to set up a Top N with a calculated rolling 3 month percentage

Thanks for catching that. Slipped my mind.

• ###### 4. Re: How to set up a Top N with a calculated rolling 3 month percentage

Quite alright--I appreciate you taking time to answer forum questions as this helps me learn and reinforce concepts, etc.

• ###### 5. Re: How to set up a Top N with a calculated rolling 3 month percentage

Awesome thank you for all the additional information regarding formatting etc.  I feel this should be easier to design in Tableau but am happy I can design it.

Thanks,

Julia