5 Replies Latest reply on Dec 6, 2012 11:42 AM by Jonathan Drummey

    Top N not working as expected

    Julia Hennelly

      I have a workbook and on one of the dashboards I would like to see the top colleges in terms of associates recruited from a college.  My first issue is that although I have a Top N as you can see in the image the data is not sorting as I would expect.

       

      Secondly when I limit to one hospital on the dashboard I only see 2 colleges although I know there are more and in fact if I make N 25 I see a couple more.

       

      The colour on the bar is simply whether the associate had an acceptable or unacceptable test when they started employment.  This way I can analyze where most of our employees are coming from and at a glance see if a college tends to provide acceptable or unacceptable employees.

       

      I am clearly missing how Top N works.  There is a filter on college to exclude bad data basically.I assume this filter would apply and then the top N would be applied.

       

      Advise?  Comments?

       

      Julia

      collegetopn.JPG

        • 1. Re: Top N not working as expected
          Frank Hettlinger

          Julia,

           

          I too have had this problem in the past. The solution that I have found that works is to figure out what order you want the filters applied in, then click on the drop down arrow from the pill in the filters menu, and select 'Add To Context'. This will ensure that your data is looking only at the data set you desire. You can't add everything to context because that won't be right either; just add the items that will limit the data set that the rest of your filters will look at. It took me a few times of playing around with this feature to get it right. See if that helps and let me know.

           

          Frank

          • 2. Re: Top N not working as expected
            Shawn Wallwork

            Julia, this is one of those little duh! moments (we all have them). You are sorting on COUNT() of Employee, but your viz is COUNTD(). Change the aggregation of the sort and it works like you want:

             

            Julia.PNG

            --Shawn

            • 3. Re: Top N not working as expected
              Julia Hennelly

              Yup a duh moment BUT if I change to count distinct - issue remains plus the problem of not seeing all colleges when N is set to a higher number but the filter is by hospital is still an issue.

               

              Thanks,

              Julia

               

              collegetopn.JPG

              • 4. Re: Top N not working as expected
                Shawn Wallwork

                Yep, the duh is on me. I didn't look closely enough at your workbook. I'll try to work it out. Meantime maybe Jonathan has some ideas.

                 

                --Shawn

                • 5. Re: Top N not working as expected
                  Jonathan Drummey

                  When Tableau does the COUNTD(Employee) for the Top N filter and the Sort on Name of College, that's happening independent of other fields in the view, like Acceptable. In the data, we can see that the same Employee can have both Acceptable and Unacceptable responses:

                   

                  screenshot3.gif

                   

                  And the total count of employees without Acceptable in the mix is actually is quite a bit smaller than the numbers shown in the bar chart or the mark labels:

                   

                  screenshot4.gif

                   

                  So, the length of the bars and the the mark labels are really based on COUNTD(Employee+Acceptable), while the Top 10 Filter and Sort are based on COUNTD(Employee), and we end up with a confusing batch of numbers and odd sort orders until we understand how Tableau works.

                   

                  I'm not sure whether you'd want to be double-counting each Employee, so I'm not sure what solution to recommend. If you are ok with double-counting, then create a field called Employee+Acceptable with STR([Employee])+" " +[Acceptable] and then use that for your measure, in the Top 10 Filter and for the Sort on Name of College. I set that up in the attached.

                   

                  Jonathan