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

    Top N Bucket

    suvas.chandra

      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
          Alan Perez

          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
            Ben Neville

            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
              Ben Neville

              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
                Jim Dehner

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

                  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:

                   

                  See the evolution of US income disparity over time | Tableau Software

                   

                  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
                    Jim Dehner

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

                      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 ?