3 Replies Latest reply on Nov 12, 2012 10:12 PM by Dimitri.B

    Top N within a group

    Julia Hennelly

      I have managed to get top colleges within a hospital in my workbook.  I now want to do a couple of things:


      1. Parametrize the value of N so user can view Top 5 within each hospital or Top 6 etc.


      2. I want to add colour coding to the viz so that the bar is stacked by acceptable/unacceptable rating which seems to throw off the Top N sorting.  Although I want sorted by rank I do not want to see the number and the rank sorting is by the pair I created not including the acceptable/unacceptable.


      I am attaching a workbook and require the colour coding.


      Any help would be much appreciated.



        • 1. Re: Top N within a group

          What would be your criteria to show a college at the top, i.e. largest number of employees with 'acceptable' rating, or largest 'acceptable' minus 'unacceptable', etc.?

          • 2. Re: Top N within a group
            Julia Hennelly

            I would like them sorted in descending order by number of employees hired from each college.  The rating of acceptable/unacceptable is simply a colour coding and should not be part of the TopN.


            Does this make sense?



            • 3. Re: Top N within a group

              Normally you would do something like

              INDEX() <= [Top N]

              and put it on the Filter shelf, selecting True, which gives you control over N in Top N.


              But in your case the complication is that the same employee can appear in both Acceptable and Unacceptable (A/U) categories, thus inflating the distinct count, i.e. if the same employee is both Acceptable and Unacceptable, he/she will be counted twice instead of once.


              The screenshot below illustrates the problem - distinct count regardless of A/U is on the left, the same but with A/U dimension added - on the right. The colleges here are sorted in the order you want, but I could not find a way to calculate an INDEX() that would take this into account.


              The other minor problem is the fact that same college can belong to several hospitals, which you tried to solve by creating a set. But you can't use a set in table calculations, so I joined two text fields instead to get the same result, and this new combined college/hospital label can be used in table calculations. But, unfortunately, everything gets stuck on the problem above.


              distinct count - total vs segmented.png