Complex aggregate calculation help

[silversky9]

Hi,

I'm using Visual Analytics 8.5.  My data set consists of student credit hour group, race, gender, student headcount, semester etc.  I have three student credit hour groups (categorical variable): 16+, 15, 12-14.  Previously, I only was tasked to look at the 16+ credit hour group.  The question was to determine the percent of students taking 16+ credit hours by race/gender by semester, and this was displayed in a line graph.  To achieve this, in effect I created new variables for each race group, new variables for each gender group, and then new variables for each gender/race combination.  For example, if I wanted to know the % of Hispanic students, I used the following aggregate calculation:

Sum [_ByGroup_] ('TTLHispanic16+'n) / AggregateTable(_Sum_,
Table(_Sum_, Fixed('Semester'n), 'TTLHispanic'n))

I created the variable TTLHispanic16+ using the following:

IF ( ( 'Credit Hour Group'n = '16+' ) AND ( 'race'n= 'Hispanic' ) )
RETURN 'Headcount'n
ELSE 0

The variable TTLHispanic was calculated similar to above.  This method was not ideal, but it worked as I only was looking at one of the three credit hour groups.  Now since I'm trying to use two more credit hour groups in my graph, it will look very messy to have multiple gender/race groups by credit hour group.  I would like to be able to get the % of each credit hour group by race (or gender, or race/gender) by semester, and this will be used in a line graph.  Ideally, each of these variables would be in a filter which the end user can select from. 

For example, for Fall 2021, the percent of (16+ credit hours Hispanic/Total Hispanic) = (478/2701) = 17.7%.  I've attached sample data to illustrate where I got this value.

Can someone help with this calculation?  (% of each credit hour group by race, etc. by semester)

Thank you.

[acordes · Accepted solution]

Use table aggregator. 

You have to convert date into character variable by using the 'format' function. 

Then construct the crossings you need. 

AggregateTable(_Sum_, Table(_Sum_, Fixed('Repairing Region'n,
'_in-service-date'n, 'Engine Model'n), 'Frequency'n)) /
AggregateTable(_Sum_, Table(_Sum_, Fixed('Repairing Region'n,
'_in-service-date'n), 'Frequency'n))

[silversky9]

Thanks so much, I was able to get what I needed. I appreciate the help!