1 2 Previous Next 23 Replies Latest reply on Jul 11, 2018 3:28 AM by Gitakshee Satija

# Select and view Top N and Bottom N on Same Worksheet

TCC 2013 was awesome and learn a lot to apply.  However, I am a bit stuck on parameters.  I am created a stock price change view based on time period (one week, one, month, YTD, etc.) and want to give viewers the option to filter by time period they want to see as well as the the Top N and Bottom N they select.  I got the parameters for Time Period to work, and got the Top N parameter.  But I am stuck on the Bottom end parameter.  I know I am supposed to create a set for Top N and Bottom N, then combine the sets, but I can't figure out how to make Top N paramter a set.  See the attached work book.

Any help would be appreciated!

• ###### 1. Re: Select and view Top N and Bottom N on Same Worksheet

Hi Christopher,

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

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:

Jim

5 of 5 people found this helpful
• ###### 2. Re: Select and view Top N and Bottom N on Same Worksheet

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.

• ###### 3. Re: Select and view Top N and Bottom N on Same Worksheet

Hi Jim,

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

Many Thanks!

José

• ###### 4. Re: Select and view Top N and Bottom N on Same Worksheet

Hi,

Drag the created set into filters shelf.

Mahfooj

• ###### 5. Re: Select and view Top N and Bottom N on Same Worksheet

Hi Mahfooj,

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.

Jose

• ###### 6. Re: Select and view Top N and Bottom N on Same Worksheet

Hi,

Share your expected output or the workbook.

Mahfooj

• ###### 7. Re: Select and view Top N and Bottom N on Same Worksheet

Hi,

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

Thanks

Jose

• ###### 8. Re: Select and view Top N and Bottom N on Same Worksheet

Hi,

You can simply create a set like this.

Right click on the field on which you want to create a set.

And then see the output once again.

You can use parameter also in place of 5 lets say Top N.

Top N will be your integer parameter.

Mahfooj

• ###### 9. Re: Select and view Top N and Bottom N on Same Worksheet

Hi,

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.

Many thanks

José

• ###### 10. Re: Select and view Top N and Bottom N on Same Worksheet

Okay!

You can try one thing. Once put context filter on Product ID in filter self then check.

Mahfooj

• ###### 11. Re: Select and view Top N and Bottom N on Same Worksheet

Thanks Mahfooj,

I have found an information very useful in another blog related to this. Thanks for your help

• ###### 12. Re: Select and view Top N and Bottom N on Same Worksheet

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...

• ###### 13. Re: Select and view Top N and Bottom N on Same Worksheet

Hi Jim

Great Post

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 )

• ###### 14. Re: Select and view Top N and Bottom N on Same Worksheet

Hello Ammi,

That 6 and 7 are indexs not Rank. When you use Rank() it will give the same number.

TG

1 2 Previous Next