7 Replies Latest reply on Oct 3, 2013 7:15 AM by Ian Pytlarz

    Aggregate variable by something not displayed

    Ian Pytlarz

      Hi,

       

      I've got a dataset where records are students who attended a class. A class is every time a group of students meet (it is essentially a concatenation of several variables), meaning for instance that a lecture with 5 recitations would create 6 classes (1 for the lecture, 5 for recitations). I need the data in this format so I can analyze things by class, and by course (meaning I'd view the previous example as one big unit, essentially ignoring duplicate students).

       

      Now I'm trying to get a count of classes by class size bins (how many classes do we teach with more than 30 people in them, etc), but I'm having trouble figuring out how to get Tableau to do this. I've created a class ID that I can do a count distinct on to get a count of classes by any dimension I already have available, but class size is something I need to calculate along Class ID and semester by counting the number of students (rows) for each class and Tableau doesn't seem to want to do this. I could just go ahead and do all that work in SAS to create a class size variable, but I prefer to do as much of the data manipulation as I possibly can in Tableau. Can someone help me out? Let me know if I haven't explained my problem properly. Thanks!

        • 1. Re: Aggregate variable by something not displayed
          Matt Lutton

          Can you post a sample or mock up workbook?

           

          By the way, I'm in Indy and work with Goodwill Education Initiatives.  I'm happy to share ideas, etc.  mlutton@goodwilleducation.org

          • 2. Re: Aggregate variable by something not displayed
            Ian Pytlarz

            Here's a mockup. What I want is to get tableau to tell me I've got 2 classes of size 5, 2 of size 2, 1 of size 10, and one of size 4. Then I can bin the sizes and get a histogram of class sizes.http://public.tableausoftware.com/static/images/co/coursedatamockup/Sheet1/1.png

            • 3. Re: Re: Aggregate variable by something not displayed
              Matt Lutton

              OK.  I deal with this type of thing often.  I've attached a possible solution.  Note that because aggregates are used in the "Class Size Categories" calc, it becomes a measure.  In order to make this work, ClassID must be on the level of detail in order to split up the Bars based on the ClassID.  I have also created bars by using Count Distinct of Class ID on the Columns shelf as a continuous pill.

               

              When you do this, any class not meeting your criteria will be NULL, so you need to filter those NULL values out.

               

              When you use this method, your bars are broken into chunks (which I absolutely HATE).  You can remove the borders on the bars so they appear as one chunk (I've done this by right clicking on the Color shelf and setting borders to "none"), but if you create actions from this bar chart to details, each chunk will represent its own class--as a work around, I instruct users to click on the bar's HEADER instead of the bar itself, and then the entire bar will be represented in the action.  Let me know if you have questions.

              I would LOVE to see another approach to this, because I really hate this action limitation.

               

              NOTE that the calculation can be used on the color shelf, as I did on sheet one.  I also used SUM(Number of Records) instead of COUNT(StudentID), but either would give you the same result, I believe.

              • 4. Re: Re: Aggregate variable by something not displayed
                Ian Pytlarz

                Is there any way to get the data to appear in a table format? This works, but as you said only visually, and even then there are some issues. I'll use this in the meantime though, thanks!

                • 5. Re: Re: Aggregate variable by something not displayed
                  Matt Lutton

                  I thought your ultimate goal was a histogram of class sizes--you should be able to get that using the technique outlined above.  A table format is a bit more difficult, again because of the individual records that make up the whole.  I am thinking there may be a way to utilize a Table Calc to return the appropriate numbers by category, but I cannot figure it out right now.

                   

                  I welcome others to help find another way.  I took this issue to the Tableau Doctor at the Customer Conference this year, and they were unable to help me at the time--then, I went back and forth with Tableau Support after the conference and I still have no other method for arriving at these bar values and using an action filter to return the individual/detailed records that represent the whole.  I would love to find another way.

                  • 6. Re: Aggregate variable by something not displayed
                    Jonathan Drummey

                    Matthew had linked to this thread from here, http://community.tableau.com/message/229782#229782, I have a solution for getting a single bar.

                    1 of 1 people found this helpful
                    • 7. Re: Aggregate variable by something not displayed
                      Ian Pytlarz

                      Awesome. Still not quite perfect of course but a lot closer. I could change the data on my end, but I can't update the database in any way (Purdue is a very large institution, changes like that are quite slow). Gives me more to think about in any case. Thanks!