Boss: This year we have done really well. We are going to send our top 10 sales guys to the Moon. Give me the list.
Analyst RM: After running my Tableau, I can only find 5 guys in the top 10.
Boss: What? You are fired.
Analyst R: I found 11 guys in the top 10.
Boss: Are you serious? We do not have budget for one more person. You are fired.
Analyst RD: There are 20 people in the top 10.
Boss: Are you out of your mind? You are fired.
Analyst RU: I got exactly 10 guys in the top 10.
RU keeps his job at the expense of some sales guys who did just as well as those who made his top 10.
The full names of the analysts are as follows:
RM: Rank_Modified()
R: Rank()
RD: Rank_Dense()
RU: Rank_Unique()
The moral of the story is that Top N can mean different things. It really depends on which Tableau rank function is used.
In terms of the number of records in Top N, the rule of thumb is
Rank_Danse >= Rank >= N >= Rank_Modified
and
consistently, Rank_Unique = N
See the picture below as the illustration of how the story could happen. The workbook can be found here. An earlier review of all the rank functions was done from a different perspective: Part 1 and Part 2.
Great summary of the various Rank functions.
ReplyDeleteRM could also be named "TOP N Filter with another (non-context) filter". This is one thing that is not always intuitive and stumps beginning Tableau users. TOP N Filters, Sets, and Fixed LOD calcs are all calculated across the entire data set (or context, if a context filter is in use).
Hmm, why is RM non-context?
DeleteAll the rank functions are table calcs, so done post-query. Using a TOP N filter is done as part of the initial query -- which is why any filters you want to constrain the TOP N filter must be context so the TOP N filter is done after them.
DeleteYou can use the Rank results as filters too and they will applied be after all non-table calc filters have been applied.
So you have a lot of flexibility with how to filter. It's just not entirely obvious at first.
My original comment was not clear. As I was reading your post, I originally thought "I can only find 5 guys in the top 10" was going to be the case of a Top N filter applied alongside other filters. To see it in action use the Superstore sample data and try filtering to the top 10 items / products using a Top N filter. Then additionally filter by Department (alternately called Category in some versions) and notice that you might only see 3 items (instead of 10) in a given department. That's because you are seeing the combination of the top 10 items overall and the Department filter. But add the Department filter to context and you'll get the top 10 items in the selected department(s)d.
Excellent comments about the Top N filter which is a filter based on the a posteriori ranking results, instead of the a priori data sets from the query.
Delete