9 Replies Latest reply on Apr 19, 2016 11:44 PM by Kenneth McBride

    Top N Items Per Customer vs. Total Master Company's Top N Items

    Mat Henning

      Hello again Tableau Community!

       

      I have (what is probably to you experts) another newbie Tableau question. I need to have a tab that shows the top N items per customer. Here is what my problem is/where I am running into issues:

       

      • This should be pretty straight forward in my explanation so I don't have to post an anonymous workbook so here is how I'd like it to look and the problems I am running into:
        • I'd like to select a date range to pull data from
          • This would provide a list of customers that purchased any product within that date range
            • Underneath the customer row:
              • A list of the specific customers top "N" items based by total purchases by the customer

       

      This is where I am running into a problem. Whenever I create my set, it is looking at the total outbound sales of all items to all customers that the master company is selling to instead of the individual customers top N items and only returning that customers top specific "N" items.

       

      I'd appreciate any help on being able to figure out how to show the individual customers top "N" items instead of the suppliers top "N" items.

       

      Thanks in advance!

       

      Mat

        • 1. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
          Rishabh Dhingra

          Hi Mat,

           

          You could use an index calculation to rank the Products under the specific customer. Place the index calculated field between Customer and Product and use both Customer and Product in the Addressing fields in the table calculation. Sort the index field based on your sort field. Then filter the index to show the top N values

           

          I have attached a sample workbook wherein I am displaying the Top N products by sales for individual customers.

           

          I believe this is what you are looking for.

           

          Thanks,

          Rishabh

          • 3. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
            Tharashasank Davuluru

            Hi,

             

            The attached workbook is in version  9.3.

             

             

            Step 1: Create a top N customers view

            1. Open a new workbook and connect to the Sample-Superstore data source.
            2. In the Data pane, right-click Customer Name, and select Create > Set.
            3. In the Create Set dialog box that opens, do the following:
              • In the Name text box, type Top N Customers by Items or product name.
              • Click the Top tab.
              • Select By Field.
              • From the field drop-down list, select product name or item name
              • From the aggregation drop-down list, select count.
              • When finished, click OK.

              style="margin-top:auto;margin-bottom:auto;margin-left:36.0pt"

              1. From Sets, drag Top N Customers by Items to the Rows shelf.
              2. From Dimensions, drag Customer Name to the Rows shelf, positioning it to the right of the set.
              3. From Measures, drag Sales to the Columns shelf.
              4. On the toolbar, click the Descending Sort button  to make sure that the set is working.

              style="margin-top:auto;margin-left:72.0pt;margin-bottom:.0001pt"

            • In the Calculated Field dialog box that opens, complete the following steps:
              • In the Name text box, type Subset Labels.
              • In the Formula text box, type the following formula to create dynamic labels for the customers in the set:

              style="margin-top:auto;margin-left:72.0pt;margin-bottom:.0001pt"

            • From Dimensions, drag Subset Labels to the Rows shelf, placing it between the Top N set and the Customer Name dimension.
            • On the Rows shelf, right-click the Top N Customers by Sales set, and then select Show Header.

              style="margin-top:auto;margin-bottom:auto;margin-left:36.0pt"

            Step 2: Combine the Top N set with a dynamic parameter

            1. In the Data pane, right-click Top N Customers by Items, and then select Edit Set.
            2. In the Edit Set dialog box, select the Top tab, click the value drop-down menu, select the Top Customers parameter, and then click OK.

              style="margin-top:auto;margin-bottom:auto;margin-left:36.0pt"

             

            Additional tips for improving the view's functionality

            Here are some additional steps you can take to give your viewers more flexibility in displaying the customer subsets.

            1. From the Data pane drop-down menu, select Create Parameter.
            2. In the Create Parameter dialog box that opens, do the following:
              • For Name, type Expand or Collapse.
              • For Data type, select String.
              • For Allowable values, select List.
              • In the List of values, type the values Expand and Collapse.

              style="margin-top:auto;margin-left:72.0pt;margin-bottom:.0001pt"

            • Select Analysis > Create Calculated Field.
            • In the Create Calculated Field dialog box that opens, do the following to create a calculation that uses the parameter you just created. This calculation enables viewers to specify how to view customers in the Others subset:
            • For Name, enter Customer Names.
            • In the formula box, type the following formula, and then click OK:

              style="margin-top:auto;margin-left:72.0pt;margin-bottom:.0001pt"

            • (optional) From the Columns shelf, drag the count([Product name or item name]) measure to Label on the Marks card.
            • From the Dimensions pane, drag Customer Names directly on top of Customer Nameon the Rows shelf, so that it replaces it.

            Now you can use the Expand or Collapse parameter control to see the list of names in the top N customers and the remaining customers rolled up into a single Others entry.

             

            • 4. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
              Kenneth McBride

              Hey Mat, there are likely several ways to accomplish this but based on the information given but I went this direction:

               

              - Create a table calculation field using the INDEX function to control the row sort.

              - Create a parameter to control your Top N selection, I called mine 'Top N'.

              - Create a table calculation field to filter to the Top N, I called my 'Is Top N'.

              - Use the Is Top N table calculation as a filter and set it to true. Right click on this filter and Edit Table Calculation then set it up like this:

              table calc.png

               

              - Show the Top N parameter control (right click on it in the parameter window and Show Parameter Control.

              - Drop the INDEX table calculation on the rows shelf, set it to discrete, move it in between Customer Name and Product Name, set it up the same way you did the Is Top N table calculation filter (this is to sort your products descending by Sales).

               

              Workbook attached. Let me know if that works for you. I also left the INDEX field header showing on the rows shelf to validate the results but you can hide that header if you want.

               

              Ken

              • 5. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
                Kenneth McBride

                Tharashasank,

                 

                In the future if you're going to copy/paste the contents of a kb article can you instead simply paste the URL and leave out the contents. When you copy/paste it leaves out screenshots and other important information.

                 

                http://kb.tableau.com/articles/knowledgebase/top-n-and-others-sets

                • 6. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
                  Tharashasank Davuluru

                  Hi kenneth ,

                   

                  Thank you . but i have edited some of the steps in the content so i did not post the link directly. I changed some of the calculated fields name and which need to be place in column shelf and row shelf for the understanding of his problem.

                  • 7. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
                    Kenneth McBride

                    Hey Rishabh, welcome to the forums! As a tip, please read the other replies to a question before posting so as to avoid providing the same answer as others. This way we might end up with several possible solutions to a problem instead of the same solution posted by several individuals.

                     

                    Ken

                    • 8. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
                      Rishabh Dhingra

                      Hi Ken,

                       

                      I had responded to this question just after a minute of it being posted and there was no other replies by then. However, I think my response got approved by the moderators just now so you might be seeing it being posted now.

                       

                      Though I understand your point

                       

                      Rishabh

                      • 9. Re: Top N Items Per Customer vs. Total Master Company's Top N Items
                        Kenneth McBride

                        My apologies Rishabh, I forgot that they still require moderation with new users.

                         

                        Ken