Expression AVERAGE()

AppSheet AppSheet Q&A
1

I’m struggling with an expression to average a list of values based on a number of conditions. I’ve gotten the expression part of the way there using COUNT(), but need to AVERAGE(). Here’s the expression thus far:

Count(Select(StephensonCourt_Tbl[CourtID],AND([CourtDate]>=Today()-7,TEXT(ANY([Related StephensonVPRAIR_Tbls][ChargeCategory]))=”Non Violent Misdemeanor”,TEXT(ANY([Related StephensonBondHearing_Tbls][ReleaseRecognizance]))=”No”)))

I need to go to the StephensonCourt_Tbl, CourtID, the CourtDate and only check records for the last 7 days, and go the StephensonVPRAIR_Tbl, ChargeCategory count those that are = "Non Violent Misdemeanor” and go to the StephensonBondHearing_Tbls, ReleaseRecognizance = “No” and go to the StephensonBondHearing_Tbls, BondAmount column and average the values in this column that match, BondAmount.

I hope I’m making sense, Any help or suggestions you might have are much appreciated.

Thank you!

2

Your expression is far too complex and imbricated.

I suggest you bring back some informations step by step from the last table to the main one.

For example, you could make:

  • column “part1” with expression:
[Related StephensonVPRAIR_Tbls][ChargeCategory]
  • column “part2” with expression:
[Related StephensonBondHearing_Tbls][ReleaseRecognizance]

and so on. That will help you into building your last expression.

Also, this part:

SELECT(StephensonCourt_Tbl[CourtID]

is strictly equivalent to:

FILTER("StephensonCourt_Tbl"

(if [courtID] is the key-column of the table StephensonCourt)

Also:

AVERAGE() requires a list of number-type values.

SELECT(StephensonCourt_Tbl[CourtID] will likely be a list of ID from the table StephensonCourt_Tbl

Once you break you main expression apart, can you share us a screenshot of the table columns from the editor ?

2 Likes
3

Thanks for your help with this. You got me on the right track after thinking it through.

2 Likes