IF function to apply a number value from a EnumLis...

Tom11 · 06-25-2021 01:49 PM

Hello,

I could use some assistance with developing an IF() statement to calculate the values entered in a different column within the same table. Basically, I have column, AggravatingFactors which is a EnumList type and the following options are available:

None
Strong gang affiliations
Serious injury to victim
Victim is a Senior
Very young or disabled victim
Specific threats to witness or victim
Victim resides in household
Addiction issues
Current emotional state

I need to calculate the values of AggravatingFactors in AggravatingScore and the total score of AggravatingScore will not exceed 3. So, if the user selects just one of the available options, the score will be 1, if the user selects 2 of the available options the score will be 2, if the user selects 3 or more of the available options, the score will be 3. If the user selects “None”, the score will be 0. I’m having trouble figuring out how to write this expression and I have two of these scenarios, so figuring this out will solve both problems. Thank you in advance for your assistance!

Steve

Maybe this?

IFS(
  NOT(IN("None", [AggravatingFactors])),
    MAX(LIST(3, COUNT([AggravatingFactors])))
)

See also:

Tom11

Thank you so much for your help, When I use either of the expressions you provided, they don’t calculate correctly.

With, MAX(LIST(3, COUNT([AggravatingFactors] - LIST(“None”)))), the score is always “3” even if “None” is selected or no selection at all, seems to be defaulting to 3. If the user selects 1 to 3 options, the score remains 3, when the user selects a 4th, 5th, 6th, etc… the score increases by 1 and should have a max score of 3.

With, IFS(
NOT(IN(“None”, [AggravatingFactors])),
MAX(LIST(3, COUNT([AggravatingFactors])))
)
If “None” is selected, no score will be counted even is the user accidentally selects other options, which is good, can prevent mistakes. But if the user selects 6 other options, the score becomes “6” and the Max is “3”.

I tried to rewrite differently:
IFS(NOT(IN(“None”,[AggravatingFactors])),COUNT([AggravatingFactors])),
IFS([AggravatingFactors]=1,1,
[AggravatingFactors]=2,2,
[AggravatingFactors]=3,3,
[AggravatingFactors]=4,3,
[AggravatingFactors]=5,3,
[AggravatingFactors]=6,3,
[AggravatingFactors]=7,3,
[AggravatingFactors]=8,3)
But have the same issue as the expression above. If “None” is selected, it will not count. But, if “None” is not selected, and all 8 other options are selected, the score becomes “8” and the Max should be 3.

Again, thank you for all your assistance with this!

Steve

Whoops! My mistake! MAX() should be MIN() instead.

Tom11

Yep, that seems to have done it. Thank you!!!

Tom11

That seem to have done it and I applied the same logic to the other scenario so solved both issues! Huge Thanks!

IF function to apply a number value from a EnumList