Counting elements of a list based on a criteria

[Patrick_Paul]

I’m trying to come up with an expression that counts the number of related trainees that have a status of “active approved”. The below expression doesn’t work because I’m not sure how to “dereference” or whatever you call it the [current status] column within the individual related trainee records. Please advise.

count(select([Related Trainees], ([current status]=“Active Approved”)))

[JuneCorpuz]

Where are you applying this formula? To a column or a name of a view?

[Patrick_Paul]

This is just a virtual column.

[JuneCorpuz]

Can you please explain it further? What table are you into? Are in the related trainees or other table?

[Patrick_Paul]

The table the virtual column exists in contains a reference to the trainees table. There is a “related trainees” column as per usual, which contains that list.

[JuneCorpuz]

Here is what I understand with your post and replies. There is a TABLE called TABLE 1 (I just named it), which has a reference table on it called RELATED TRAINEES. Is that correct?

[Steve · Accepted solution]

Try:

count(select([Related Trainees][_ROWNUMBER], ([current status]=“Active Approved”)))

[GreenFlux]

Steve:

select([Related Trainees][_ROWNUMBER]

Interesting! I’ve always used SELECT(TableName[ColumnName],...).

I didn’t realized you could use SELECT([Ref_Rows][ColumnName]...!

[Steve]

See Constructing a List from a List Dereference here:

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

[Patrick_Paul]

That worked. Let’s take it a step further now. I want to add the total number of “active approved” trainees to the number of “graduate” trainees that graduated in the current year.

count(select([Related Trainees][_ROWNUMBER], ([current status]=“Active Approved”))))+count(select([Related Trainees][_ROWNUMBER], (and([current status]=“Graduate”, year([graduated])=year(now()))))

The system basically ignores the second part altogether, stopping with only evaluating the first “count” function. No errors, just ignores it…?

[Steve]

Is that your actual expression? The parentheses are mismatched. Try this instead:

count(
  select(
    [Related Trainees][_ROWNUMBER],
    ([current status] = “Active Approved”)
  )
)
+ count(
  select(
    [Related Trainees][_ROWNUMBER],
    and(
      ([current status] = “Graduate”),
      (year([graduated]) = year(now()))
    )
  )
)

[Patrick_Paul]

-Regardless of my usage of parenthesis or matching Steve’s, the expression ignores the plus sign and everything after it. It does not generate an error, it simply does not even consider it.

-I decided to create an additional virtual column just for the second expression:
count(select([Related Trainees][_ROWNUMBER], (and([current status]=“Graduate”, year([graduated])=year(now())))))

-The first virtual column works fine (where we’re just counting active trainees). The new column, with the “current year” grad count, does not. It generates no error, but it returns no results (always zero). The parser may be having trouble with it because this is what the parser shows, which seems awkward:
COUNT(
…The list of values of column ‘_RowNumber’
…from rows of table ‘Trainees’
…where this condition is true: (ALL these statements are true:
…1: (The value of column ‘Current Status’) is equal to (“Graduate”)
…2: (YEAR(
…The value of column ‘Graduated’)) is equal to (YEAR(
…NOW()))))

[Steve]

Please post a screenshot of the complete expression.

[Patrick_Paul]

I actually scrapped it and retyped it and suddenly the parser “liked” it…?

[Steve]