4 Replies Latest reply on Jul 28, 2016 1:34 PM by K Muntean

    Unique Count of NON-selected items

    K Muntean

      I am a Tableau novice, but have been asked to design a data extract for use in a Summary Page for an existing Tableau workbook. I have attached an example of the data as it currently stands.

      What I need to know is how to calculate the following item, and specifically, what I need to ask for in terms of data to make that calculation as simple as possible.

      1. Waitlisted for other courses:
        • These items should show the count of unique students (Masked ID) who are waitlisted for X number of courses NOT selected in the filter (which allows multiple selections)

       

      Current Term Course Waitlist Summary

      Drop-down selectors (can select multiples): Subject, Course Number (or a Subject-Number concatenation, if that makes calculation easier)

       

      Unique headcount of students on a waitlist for selected course(s): Domestic  ______________    International  ______________

      Students on a waitlist for selected course(s) are:

      Waitlisted for other courses

      No other courses

      Count of unique students who are not waitlisted for any courses other than those selected in the filter

      1 other course

      Count of unique students who are waitlisted for 1 course in addition to, but not including, those selected in the filter

      2 other courses

      Count of unique students who are waitlisted for 2 courses in addition to, but not including, those selected in the filter

      3 other courses

      Count of unique students who are waitlisted for 3 courses in addition to, but not including, those selected in the filter

      4+ other courses

      Count of unique students who are waitlisted for 4 or more in addition to, but not including,  those selected in the filter

        • 1. Re: Unique Count of NON-selected items
          Joe Oppelt

          I'm kind of guessing what you really need here.

           

          Attached I have created a very basic example using the superstore sample data.  (Workbook is 8.2, but will upgrade to any version you are on.)

           

          Sheet 3 is a very basic count showing how many rows are in the data for each continent.  (I did that count two different ways:  COUNT(Continent) and [Number of records].)  I also added a COUNTD() (which in this example will always be 1.  But it shows the difference between COUNT and COUNTD, and shows that you can do the same thing for your example.)

           

          Sheet 4 is a copy of Sheet 3, but it has a filter added.  It's an "exclude" filter, so whatever continent you select, it gets excluded from the sheet.  The user will select whichever values he wants to exclude, and your sheet will count up whatever is left.

           

          Sheet 5 is a copy of Sheet 4 burt has an extra filter added.  It is also an exclude filter, and it is on [Region].  Only "North America" has multiple regions.  All the other continents have just one region:  "International".

           

          Now when you select regions, the COUNTD and COUNT count up what it left.  I also added a COUNTD([Region] to show the effect on that field.

           

          Is this the same sort of thing you are trying to accomplish?

          • 2. Re: Unique Count of NON-selected items
            K Muntean

            Thank you for the quick response!

            I think the exclude filter is close, but not quite what I am looking for. I have (actually, this time) attached to the OP a sample data set and a word draft of what the exec want the front page to look like. As you can see, to maintain intuitive usability and generate the other numbers on the page, the user needs to select the courses they ARE interested in (not those they want to exclude). I think I need the formula to do the excluding, rather than the filter?

            • 3. Re: Unique Count of NON-selected items
              Joe Oppelt

              In the attached I have changed the behavior to work on "include" filters instead of "exclude" filters.

               

              What I did requires Tableau 9.0 or higher to have access to LOD capabilities.  The two calcs that do what I am doing here are LOD calcs.

               

              The one that tells me how many regions are in the database acts outside of the filter behavior.  The "FIXED" function of LOD says to query the whole data set.

               

              The one that tell me how many distinct regions were in the selected table uses "INCLUDE".  As it is written, It says to do a COUNTD of all the rows brought into the table.


              In the SUPERSTORE data, all continents have only one region (international) except for North America, which has all 5 regions.

               

              If you play with the filters. you will see that "Regions in database" never changes.  It's always 5.  If you add in other continents (besides North America) you'll see that the unique count of regions always remains at 1.  If you add in North America, (and if you have only 4 regions selected, as I have it uploaded here) then the number of regions selected is 4.

               

              Of course, [Database...] - [Selected...] will tell you how many were NOT selected.

               

              The one condition you might have to accommodate is if your filter selections result in NO rows.  (Take out North America, and then take out "international" from regions in this example.)  If this is something that might occur, we can discuss how to handle that.

              1 of 1 people found this helpful
              • 4. Re: Unique Count of NON-selected items
                K Muntean

                Thank you! That is exactly what we needed to make it work, and bonus, it works with *existing* data