2 Replies Latest reply on Mar 19, 2012 7:21 AM by ernesto debeaumarchais

    Top/Bottom 10 issue

    ernesto debeaumarchais

      So I have two reports that have a Department, Section (below Dept), and some data.  I want the reports to show the top and bottom 10 Sections based on a Gain/Loss measure.  So I do a top filter on Section, and put the other filters to 'context', and the top 10 report works fine. 

       

      With the Bottom report, the issue is that there are a few Sections that are missing names, and get set to "Unknown".  The bottom report displays more than 10 Sections, and the extras come in with the "Unknown" Sections.  If I count the unique Sections, there are 10. 

       

      So it looks to me like the way Tableau handles this is almost the same as you would in SQL, with a subquery?  Select distinct top 10 Section, sort by Gain/Loss Ascending, which gives you a list of 10 Sections.  Then a main query where Section  = (Section list from first query).  So if there are any duplicate section names across departments you will get more than 10 records.  Do I have that right?

       

      In a way, I know it's a data issue, in that I shouldn't have Unknown Section names.  But what if I have the same valid Section name in two different departments?  In SQL I would fix by: Select distinct top 10 Dept, Section, sort by Gain/Loss Ascending, which gives you a list of 10 Dept/Sections.  Then a main query where Section  = (Section list from first query) and Dept = (Dept from first query).

      Is there some setting in Tableau to mimic that?

      Or do I have to create a calculated field that combines Dept/Section into one field, so I don't get duplicates?