2 Replies Latest reply on Oct 5, 2017 3:12 PM by Bekki McCormick

    Figuring out Top N in 2 columns?

    Bekki McCormick


      I’m new and struggling with analyzing data in tableau.  Specifically drilling down to top and bottom 10 data. I was hoping someone could help me in figuring out how I can drill down to find the top/bottom 10 within the top/bottom customers.


      I have a specific example of where I need help in the attached workbook.


      Looking at “YOY” tab  - I’d like to be able to treat this tab as a mpivot table.  I would like to only see the Top 10 and bottom 10 "Member Locations" based on the YOY $ sales of 2016 over 2015.


      Then of those top/bottom member locations – I would like to only see the top/bottom mfr’s roll up for those member locations. That would allow me to isolate which products are driving these results .


      The reason I’m in need of this is, not so much for a viz, but more so for a narrative report. I need to pinpoint our good and bad member locations and the products that are driving these results. On the flip side, I will also need to do this with QOQ results, then MFR roll up vs member roll up, and other scenarios. I'm no longer able to do this in Excel because the data is too large.  


      In researching top N - I added a 2016 YOY sales calculated field. Then I chose to use a sets to create the top/bottom 10 for the member location field based on the 2016 YOY $'s.


      But first - In order to add a 2016 YOY calculated field I had to add 2 calc fields to get the individual 2015 sales and 2016 sales, is this the proper way to do this?


      Next, and desperately, what I really need next is to go another top/bottom level and get the top/bottom 10 of the Mfr Roll up for EACH one of those locations. Not the top/bottom Mfr Roll Up for the overall
      sales amount.


      Is this possible? Thank you for any help you can offer!


        • 1. Re: Figuring out Top N in 2 columns?
          Amber Loranger

          Hi Bekki,

          I think that a lot of what you're looking for can be done more simply, using some of Tableau's built in functionality.

          I would recommend looking at Top 10 and Bottom 10 on separate worksheets (perhaps combining on a dashboard) which will be easier than putting them together on one worksheet.


          First you'll want to change the data type of your Order Date field to Date - that way, you may not need all the date calculations. Right-click it in the Data pane and select Change Data Type > Date. (i'm not sure why it wasn't recognized as a date automatically, there might be a strange entry in there, but I didn't see one)


          After that, you can use the Order Date field set to Year  on your view to split the view up. I've demonstrated this in the attached workbook on Top 10 and Bottom 10.

          I'm not sure if I chose the right fields to filter by for the numbers you want, but the method should help.


          To get only the top or bottom for 2015 and 2016, I put the year (2015 and 2016) on Filters under Context (this causes the filter to be applied before the Top N filter) and put Member Location on filters Top 10 by Sales / Bottom 10 by Sales.

          You could also filter the Top or Bottom 10 by 2016 YOY Sales (in this case, that calculated field will be necessary.)


          I used the information in this article: Unexpected Results when using Top N Filter with Other Filter | Tableau Software 

          In order to show the top N within a category, you'll want to use the method here: Finding the Top N Within a Category | Tableau Software 

          And this page has help about table calculations - in many cases you can apply calculations directly to the data in the view, and don't need to create a calculated field: Transform Values with Table Calculations


          hope this helps!

          - Amber

          • 2. Re: Figuring out Top N in 2 columns?
            Bekki McCormick

            Thank you so much for you reply and your workbook!!


            The issues I have using this method is that 1) I believe the sorting is based off 2015 sales and 2) I then need the MFR Roll up filtered by the top and bottom sales - within the specific members locations (not the top/bottom mfr of overall sales).


            But I love the idea of using 2 worksheets and the filters are much more simple!