3 Replies Latest reply on Oct 26, 2012 7:23 AM by John Munoz

    Displaying Top N Products at Different Levels Independently

    Ian Weiner

      Hi,

       

      I am wondering if someone could help me with an idea of how to display the top N products at different levels independently.  Please see the attached image. 

       

      In my example, I have two different levels I want to display the top 5 products independently.  The first level is an Area, Green Bay.  The second level is a store that belongs to that Area, Cheesehead Electronics.  I would like to filter by Store name to fetch the top 5 products for the Area and store, display it in one view.

       

      Is there anyway to do this in Tableau?

       

      Thanks,

       

      Ian

       

      Cheesehead_Electronics.jpg

        • 1. Re: Displaying Top N Products at Different Levels Independently
          John Munoz

          Hi Ian,

           

          I came across a similar problem recently and have been helped along by Joe Mako. I figure it's time I start paying it forward, so, here we go.

           

          I've attached the Super stores workbook to this message with a rank selector showing the top N number of Categories for each City. To get the ranking to work properly--I don't think this can be done by sorting on sales by category and using Tableau's built in top/bot N feature--I created a calculated field that is:

           

          if isnull(lookup(sum([Sales]),0)) then PREVIOUS_VALUE(0) else PREVIOUS_VALUE(0 )+1 end

           

          As I understand it, the calculation above sets the first category for each City/Category pair to 0 and then adds 1 to that value for every record that isn't the first (null) one.

           

          Then I edited that calculated field by entering the Advanced Table Calculation menu as seen below:

          table calc.PNG

          advanced table calc.PNG

          table calc 3.PNG

          After setting the compute using to City and Category (the order along doesn't seem to impact anything for this instance), I set the table calc to reset every City. That gets the ranking to start over each time the city iterates.

           

          Then added that new field, named, Sales by City Rank, in between the pills for City and Category. I made the new field discrete so it displays nicely. If Tableau sees is as continuous it wants to make a bar chart.

           

          Next, to select the Top N, I made a parameter named, Top N Selector and added it to the Viz (right click on the parameter and click, 'add to sheet') and made another calculated field named Top N for Sales with this formula: [Sales by City Rank]<=[Top N Selector] and set it to 'True'. Now, when the Top N. Selector is set to something, the filter automatically filters out any rank that's outside of the selector.

           

          I hope this helps you along your way with your challenge. I think you can use the logic that I applied to category and city to your stores data set and use action filters to tie both your visuals together.

           

          --John C. Munoz

          1 of 1 people found this helpful
          • 2. Re: Displaying Top N Products at Different Levels Independently
            Ian Weiner

            John,

             

            Your answer was very helpful. I am going to build on your example to achieve the end result I am looking for. I attached a new version of your workbook.

             

            I displayed the Top N Products at the State level. I created a dashboard to show both sheets on one view. The goal was to do some analysis at different levels. Maybe the city's top seller was Technology but their state's top seller is Office Supplies (so the city has an opportunity to sell more Office Supplies).

             

            Again, I really appreciate your help. I will be attending the conference in November. Hope to see you there!

             

            Thanks,

             

            Ian

            • 3. Re: Displaying Top N Products at Different Levels Independently
              John Munoz

              Glad I could help Ian. I will be there too. Hope we get the chance to meet up. Follow me on Twitter @johncmunoz and I'll DM you when I'm there.