5 of 5 people found this helpful
You can accomplish this Top N / Bottom M a couple of different ways: one is to use a table calc filter using INDEX() and SIZE(); another is with sets.
It looks like you started down the first path with your calculated field Top N =
[Index]<=[Parameters].[Top N] // where [Index] == INDEX()
If you add another parameter for Bottom N, you could then modify this formula to get Top and Bottom =
[Index] <= [Parameters].[Top N] OR (SIZE() - [Index]) < [Botton N]
Both of these fields should be set to Compute Using > Name when added to the worksheet. In this case SIZE() returns the total number of members of the domain (the number of names / stocks).
These calculated fields are called "table calc filters" because they contain table calc functions INDEX() and SIZE(). Unlike regular filters, which Tableau can apply at the data source, table calc filtering is done inside of Tableau. All of the members of the Name dimension are retrieved from the data source, partitioned, sorted and then filtered. This is often a disadvantage when dealing with large data sets, but it can also be an advantage. In the below chart, for example, you can still use INDEX() to show the rank of the bottom names to show viewers how many stocks / names are in the dataset.
Using sets is probably the preferred solution to the Top / Bottom problem. One reason is that unlike table calc filters, members of a set are computed in the data source and, therefore, should be much more efficient---although it won't make a much of a difference with a few hundred data points.
Sets confused me until I began to think of them as "reuseable filters." Today I almost always create a set from a filter. In your example, I started with Name on the Rows shelf and Parameter Calc on the Columns shelf. Then I Ctrl-clicked Name and dragged it to the Filter shelf and selected Top N by Parameter Calc.
To convert this filter to a set, click on Name in the Filter shelf and select Create Set. I called it Top N by Parameter Calc.
Now that you have one set, it's easy to create a second set for the bottom n. Click Top N by Parameter Calc, select Duplicate (a shortcut I like is to right-click > D > enter). Then click on the duplicate parameter and select Edit (or right-click > E). Change the name and change Top to Bottom and the parameter to the Bottom N parameter.
Now you have two sets, one for the top N and one for the bottom N. Ctrl-click both sets > right-click > Create Combined Set and select All Members in Both Sets.
Now you can drag this new set to the Filter shelf, replacing the original Name filter. And you should see the same view that you had in the the table calc version:
Thank you so much and I certainly appreciate your taking the time to show me the more the more efficient method and explain the difference between that and the path I was going down by not using set.
Hi, I am trying to show the top and bottom products according to sales, but the problems is, when I try to do this by sets it seems that the values I obtained are wrong.
Obtaining the Top N products is easy, just using a Rank and filtering this to show the top 5 (For example), but when I do this using "sets" the result it does not show the correct ranking. What am I doing wrong?, can you help me please?. I would like to find the bottom values as well, but I do not trust in this method anymore.
Top 5 (using Rank)
Top 5 (using sets), there it can be seen that the values are different
Drag the created set into filters shelf.
Thank you for your reply, I did it. But, since the moment I establish the condition (Top 5) in the filter, it show the wrong values.
Share your expected output or the workbook.
In the Workbook you will find in the first tab the expected output, and in the second tab the result obtained using the set. You will see that it is different
Expected and "correct" top 5
Top 5 obtained using "set" defined. Please, look that the top values are wrong
Sales Example.twbx 212.1 KB
I am sorry Mahfooj, I know how to create a set, and I did it in that way, but the problem is that the set created does not work, It is not showing the correct Top (or bottom) values.
You can try one thing. Once put context filter on Product ID in filter self then check.
I have found an information very useful in another blog related to this. Thanks for your help
I am trying to achieve Top N for
1. Two dimentions that are concatenated e.g. [A] + "-" + [B]
2. Combined Fields
I use the Top N parameter in filter option for combined field however output seems to be different when top 5 or top 10 show in comparison to using index().
Index() does not allow me to filter for some reason.
Just one of those days where i just cant think...
but i have one concern like in your chart and same thing for me also..
if we look your chart for value (2.1) showing 2 names like
Name Rank Value
citi group 6 2.1
cignature Bank 7 2.1
as per my concern Rank should be same for both? suppose if i select Top 6 the answer should be (Citi group )
what about cignature Bank ?
That 6 and 7 are indexs not Rank. When you use Rank() it will give the same number.