6 Replies Latest reply on Jun 24, 2016 1:09 AM by Dhruva Joishy

    How to Create Sets for seperate Top N for each of the Catagories

    Dhruva Joishy

      Hi,

       

      I found the below link in tableau Knowledge base. I want to do the same thing but separately for different categories.

       

      Create Sets for Top N and Others | Tableau Software

       

      I know I can use the Index to sort my data separately for each of categories and have my index as a filter to pick the top N data, but I have 2500 data samples and I am only interested to see top 5,10, 20 from the entire data and the % compare to total.

       

      Please can someone help as I couldn't find anything for this combined issue. I can see 2 separate answers for sorting data with Index and the above link with set. I am looking to combine this 2 solutions in one.

       

      Regards,

       

      Dhruva

        • 1. Re: How to Create Sets for seperate Top N for each of the Catagories
          Mahfooj Khan

          Is that you are looking for?

          Let me know If you've any query. Workbook (version 9.2) attached.

          You can get the % contribution using below formula

          SUM([Profit (copy)]) / TOTAL(SUM([Profit (copy)]))

           

          Mahfooj

          • 2. Re: How to Create Sets for seperate Top N for each of the Catagories
            Carl Slifer

            Howdy Mahfooj,

             

            Do you want to view all the categories top 10 at once? Or each category individually?

             

            If you want to view all the categories at once what we will use is a RANK() function. Because rank is a table function we can control its scope and direction. When you use top 10 from the filters shelf or via sets it looks at the entire database. In our case we can only make it look at each individual pane, which will be determined by the category.

             

            1) Drag Category to Rows Shelf,

            2) Drag Customer Name to Rows Shelf

            3) Drag Sum of Sales to your columns shelf

            4) Drag another instance of sales to your labels shelf of the marks card

             

            Here is where the 'tricks' come into play.

            5) Right click the sales field that is on your labels shelf and choose 'quick table calculation'

            6) Then choose rank

            ((Right now we are ranking the length of the bar that is associated with each customer in each category for the entire table, we want each category to be viewed separately))

            7) Right click that rank and go down to compute using and choose pane down

            8) Hold ctrl+left click and drag this field to the filters shelf

            9) Select the range of values you want to see, I chose 'At Most' and 5

            10) On your labels shelf Right click the calculation and select discrete

            11) Drag this field onto the rows shelf between Category and Customer Name

            12) Right click this field and uncheck the 'show header' option.

             

            What we've done is created a table calc that will work individually for each category/pane.  Then we filtered by this calculation so that the top 10 based on this calculation, based on our pane/category is shown to us. Next we have to have a way of ordering them and we order them by understanding that we are sorting left to right on our rows shelf. Category comes first and it is most lijkely in data source order (alphabetical) and then customer name is in alphabetical as well until we put our table calculation between them. The table calculation between them makes it so that the values are sorted with regards to the table calc which are based on the sum of sales for each pane. (IT sorts in numerical order by default)

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: How to Create Sets for seperate Top N for each of the Catagories
              Mahfooj Khan

              I think I have forgot to consider the Category part. Thanks for reminding that.

              • 4. Re: How to Create Sets for seperate Top N for each of the Catagories
                Dhruva Joishy

                Hi Mahfooj and Carl,

                 

                Thank you so much for your response. Really appreciate it.

                 

                Apologies for my limited knowledge of Tableau with just couple of months experience but I still need more help with the above question.

                 

                Continuing from what Carl has responded, please find attached twbx file where I have created 2 more worksheets (Using Sets and Individual Category).

                 

                Using sets:

                I have created this worksheet using the solution(link) given in my original question. It works fine when it is applied to the overall data but it doesn't work when i want it to the Category level. I understand that Sets only works with one 'Dimension' and I think that's exactly is the issue here.

                 

                * Is there any way to get this done? I want to see the top 5,10, 15, 20 customers and a section for Others which will have rest of the customers for each category.

                 

                Individual Category

                As the above looks difficult and impossible for me to do it. So, I decided to work on a way to show each category separately and achieve the expected result.

                It works perfectly fine as you can see in attached file, but my only issue is the '% of total' doesn't work when I use my 'Expand or Collapse' parameter to show collapsed view. For some reason '% of total' for Others is showing 100% instead of 96.50% (As per the example attached). But this works fine when I select Expand option and the % shows perfectly for each of the individual Customers.

                 

                * Is there a way to get the 96.50% for Others?

                * How can I show total of each category in the tooltip? For this example, I want to show $ 742,000 on the tooltip in 'Individual Category' worksheet.

                 

                Thank you once again and hopeful to get this sorted before the due date.

                 

                Regards,

                 

                Dhruva

                • 5. Re: How to Create Sets for seperate Top N for each of the Catagories
                  Swati Chhabra

                  Hi Dhruva,

                   

                  The above requirement can be achieved by using Rank, size, Window_Sum and Total as below:

                  size() gives you the total count of values you have in the list

                  and window_sum() provides sum for the values from start (0 as current position) to the nth value (n as specified) e.g., window_Sum(sum(sales),0,2) will provide you sum of sales from the current position to next to values.

                   

                  For achieving

                  I want to see the top 5,10, 15, 20 customers and a section for Others which will have rest of the customers for each category.

                  create separate calculations for Rank, size and Sales for Segment and Customer like below.

                  ---rank by Segment and Customer

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

                  -- size by segment and Customer

                  size()

                   

                  Before creating the measure for sales for segment and Customer change the default calculation for both rank and size as below:

                  In Compute Using select advance and add Segment and Customer (or any other field according to how you are displaying the columns)

                   

                  Once done, change the At the level and Restarting every values like below:

                   

                  After doing this for both Rank and Size create sales against segment and customer like below

                   

                  -----sales against segment and Customer

                  if [rank Segment Customer]<= [Top Customers] then SUM([Sales])

                  ELSEIF [rank Segment Customer] = [Top Customers]+1 then

                  WINDOW_SUM(SUM([Sales]),0,[size])

                  ELSE NULL

                  END

                   

                  and set the default calculation for this measure similar to above. Drag this to the view along Segment and Customer Name. You would notice that the top 6 Customer have values and the 6th has the sum of values from 6th to the end of list of Customers.

                   

                  Now create a Top and Others field as:

                  if [rank Segment Customer]<=[Top Customers] then ATTR([Customer Name])

                  else "Others"

                  END

                   

                  Use this next to Customer Name and filter Sales value as Non -Null Values and hide header for Customer Name.

                   

                  For achieving:

                  Is there a way to get the 96.50% for Others?

                  * How can I show total of each category in the tooltip? For this example, I want to show $ 742,000 on the tooltip in 'Individual Category' worksheet.

                   

                  This can also be achieved by ranks and Total create rank and size calculation similar to above, the difference being use Category instead of Segment, so as to filt the calculations for Category and Customer for Profit

                   

                  for Totals use Total(sum(Total)) and set similar Default calculations. and then divide this to the Profit calculated using rank and size. [Profit Category Customer]/[Total]

                   

                  Please see the attached file with 2 sheets created showing different calculations in Individual Category Profit % and Sheet 4 using rank.

                   

                  Regards

                  Swati

                  • 6. Re: How to Create Sets for seperate Top N for each of the Catagories
                    Dhruva Joishy

                    Thank you Swati... It worked perfectly...

                     

                    Regards,

                     

                    Dhruva