IF function to apply a number value from a EnumList

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
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!

Maybe this?

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

2 Likes

Or just this:

``````MAX(LIST(3, COUNT([AggravatingFactors] - LIST("None"))))
``````

If they donâ€™t choose any aggravating factors, or if they choose only `None`, the result is `0`; otherwise, the number is the count of factors that arenâ€™t `None`, to a max of `3`.

2 Likes

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

1 Like

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!

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

1 Like

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

1 Like