Basic Set Analysis Question

[#1]

I've got a peculiar request. Not completely sure how to explain it, so I'm just going to lay out as much detail as possible. I would sincerely appreciate any guidance that you could provide. My guess is I need to write a Set Analysis Expression.

Basically what I need to do is write an expression that calculates the average score of anyone with a certain flag. In this example, I want to write an expression that calculates the average score of all seniors in the database. Then I want to display this value in a table. What I need your help with is understanding how to write the expression. So how would I write this?

Database Structure

[Name] [Status] [Score]

Sample Database

Mike - Senior - 99
Susan - Underclassman - 76
Charles - Senior - 85
Kathy - Underclassman - 68

Desired Data Table/Object
I need to create a data table that displays Name and Status, but also includes a calculated field that is the average score of all seniors. This average score would actually be displayed next to Underclassmen as well as Seniors. Basically what I'm doing is saying what if everyone's score was the same as the average of the Senior's scores. The columns in this table would be:

[Name] [Status] [Average score of all seniors]

Data Table - Example
It would look like the following, in this example.

Mike - Senior - 92
Susan - Underclassman - 92
Charles - Senior - 92
Kathy - Underclassman - 92

[#2]

I would make a variable in the script like:

LOAD
avg([Score]) as 'Senior Average'
RESIDENT tablename
WHERE [Status]=Senior;

LET vSeniorAvg = peek('Senior Average',-1,'Table 1');

Then whenever you use vSeniorAvg it would be 92. There are probably 18 different ways you can do this and depending on if there is anything else you need to do this might not be the best way. You could also do:

Table1:
LOAD
[Status]
avg([Score]) as 'Avg Score'
RESIDENT tablename
GROUP BY [Status];

Then use some combination of set analysis and total to probably do the same thing but you didn't say you would need to use the avg. for underclassman as well so I don't see a need to go any deeper.

[#3]

Thanks for responding. I sincerely appreciate it. I see where you're going with this and kind of get it. Although I have what is bound to be a stupid question: I get a 'Table Not Found' error. Why is that?

*I should note: Score and Status actually live in two different tables linked by Name.

(using your first code example)

[#4]

I just wrote "Resident tablename" because I don't know the names of your tables. after the word "Resident" write the name of the table previously loaded in the script that you want the data to come from.

[#5]

That's kind of what I was thinking. The error I'm getting now is:

Field not found - <Senior>

However the table does have a field named Status with data as Senior in those fields. Any idea what I might be misunderstanding here?

Thanks again for your time responding to these questions. I can't tell you enough how grateful I am.

[#6]

I'm not sure how you got that error unless you changed the code from what I put but yeah you need to put [Status] where you have Senior probably but without seeing it I'm not sure.

[#7]

Try this: Avg ( {$<Status={"Senior"}>} TOTAL Score)