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.
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!
Not a problem!
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'
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: