4 Replies Latest reply on Sep 27, 2013 9:26 AM by Pedro Machado

    Calculation to total certain elements in a group

    Stephen Jones

      I have a data set showing the majors of college students enrolled in specific courses. I need to find the following:

       

      total art history majors, by course

      total visual arts majors excluding art history majors by course

      total of all those not included in the above two totals

       

      I am new to Tableau. Can anyone help me create a calculated field to do this? I've attached a packaged data file.

       

      Thanks,

       

      Stephen

        • 1. Re: Calculation to total certain elements in a group
          Matt Lutton

          There are a variety of ways you could approach this.  I attempted to set up the three requests in the attached.  In the first two, there are simple calculations to see if the major field "CONTAINS" or "NOT CONTAINS" the values you specified--these String Calcs are case and space sensitive, so you can use other functions like "UPPER", "LOWER", "TRIM", etc.  to ensure your criteria is what you need.  In the third, I wrote a similar calculation that returns the number of records excluding the other two criteria you specified.  Note that in the first two calcs, I used an IF statement and filtered out the NULL records that were returned for majors that did not match the criteria specified.

           

          If this is not accurate, I may have misunderstood what you were looking for.  Again, there are other ways you might approach this, but since you asked specifically about writing calculations, that is the approach I took.  Someone else may have a better/more efficient way.

           

          I work with K-12 school data, so if you're ever interested in discussing Visualizations/Tableau, feel free to email me at mluttonATgoodwilleducationDOTorg.

          • 2. Re: Calculation to total certain elements in a group
            Stephen Jones

            Matthew,

             

            Many thanks for your help. It appears to me to be correct. I'll study it further and see what I can learn about writing calculations. 

             

            All best wishes, and again, THANK YOU!

             

            Stephen     

            • 4. Re: Calculation to total certain elements in a group
              Pedro Machado

              Stephen,

               

              Here is a different approach that uses calculated sets, set combinations, and sets in calculated fields.

               

              I noticed your data doesn't have student ID. Ideally, you would create the sets based on Student ID, but I did it based on Program Description. You may still get accurate numbers assuming each student can only have one major and that each row represents a student. I recommend you double check the numbers.

               

              Here is what I did:

               

              1) Defined a set of Art History Majors

              2) Defined a set of Visual Arts Majors

              3) Combined 1 and 2 into Visual Arts Majors Excluding Art History Majors

              4) Defined a set combining Art History Majors and Visual Arts Majors

              5) Created calculated field:

              [Major Group] =

              IF NOT [Visual Arts or Art History Majors] THEN 'Other'

              ELSEIF [Visual Arts Majors Excluding Art History Majors] THEN 'Visual Arts Majors Excluding Art History Majors'

              ELSEIF [Art History Majors] THEN 'Art History Majors'

              ELSE '?'

              END

               

              I noticed my numbers are different from Matthew's. I have to go and don't have the time to see why.

               

              I like the set approach because it seems easier to maintain. Also, if you had to determine set membership for say a student based on an aggregation of other records, the set approach allows you to do these intersections, unions, etc at the student level before doing other calculations.

               

              Let me know if you find any mistakes in my workbook.

               

              Here are a couple of useful articles about sets:

               

              Let's Talk About Sets | Tableau Software

              Creating Sets for Top N and Others | Tableau Software

              New in 8: Sets, Groups, Filters and the Marks Card | Tableau Public

               

              Pedro