7 Replies Latest reply on Nov 8, 2017 10:29 AM by suvas.chandra

Top N Bucket

I want to create a calculated field and give as filter for the end users.

Calculated Field/Filter value: Top 10%, Top 5%, Top 20%, Top 25%

So, I need to create some calculated fields for top filters. What is the best way to do this ?

For example, Top 10% by Sales.

• 1. Re: Top N Bucket

the best option is a friend parameter, there you can put any value and it works as a filter for the user

• 2. Re: Top N Bucket

I'm going to assume your metric is actually called Sales, but substitute whatever name you like wherever "Sales" appears below.

Sort the values in a descending order based on your metric (not needed but usually makes the most sense). Then create a calculation called Sales Rank Percentile which ranks based on your metric. It should be something like:

RANK_PERCENTILE(SUM([Sales]),'desc')

Then creates a parameter which refers to this Rank Percentile calculation and has values from 1-100. Finally, create a filter calculation which will exclude the values which don't meet your parameter filter condition called Sales Rank Filter. Use a calculation like:
[Sales Rank Percentile] <= ([Top N %] / 100)

Put this on your filters shelf and keep True values.

Also, if you want to format your filter as a %, edit the parameter, click the display format and go to Number (custom), then choose 0 decimals and enter a % in the suffix box.

• 3. Re: Top N Bucket

Also - if you wanted Top 5/10/15/25%, just adjust your parameter to have steps of 5. Mine was set up as an integer parameter with a range of 1-100, which means that any whole number between 1-100 is a valid input.

• 4. Re: Top N Bucket

Hi

I think I understand what you are asking  - I think you want to identify the customers that make up the "Top N% of sales" and not just the the top n customers

That is a little trick - see the attached superstore example

First calculate the % of total sales for each customer as

Next set up your viz and sort by sales descending

Then create another calculated filed for the running total as

Now you wanted the user to be able to select the percentage so we need a parameter - I just used 10, 20 30 -

Note the value passed in the parameter is the decimal equivalent (you can make the list as long as you want)

And we use that value in a Boolean filter

And apply that filter to the viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 5. Re: Top N Bucket

Actually, This top N will be used in line chart. It has to be a filter. Parameter will not do. There can be multiple selections.

If the users choose top 10, the line chart for Top 10 will appear. Likewise, top 5 and top 20 and so on.

One of the example is this:

In this case, Category is given from backend. In my case, I have to calculate it in Tableau. Dashboard should be pretty much similar. Well, management has seen this dashboard and made the requirement.

• 6. Re: Top N Bucket

Help me understand - in your problem statement you said you wanted to

Calculated Field/Filter value: Top 10%, Top 5%, Top 20%, Top 25%

So, I need to create some calculated fields for top filters. What is the best way to do this

Now you say it is a line chart -

Jim

• 7. Re: Top N Bucket

I will have five calculated fields:

Cal 1: Top 5%

Cal 2: Top 10%

Cal 3: Top 15%

Cal 4: Top 20%

Cal:5: Top 25%

Then I will have the sixth one: Top N

I should be able to use Top N as a filter. Top N will have the values like: Cal 1, Cal 2, Cal 3, Cal 4, Cal 5

Then I will use Top N to create line charts with combination of others. If users select Cal 1, then the line for Cal 1 should be displayed. If they select Cal 2, then the line for Cal 2 should also be displayed....and so on.

Makes sense ?