8 Replies Latest reply on Feb 27, 2016 7:11 PM by sccardais

Calculation to count fields meeting defined criteria.

I am trying to create a calculation that shows how many fields in the same record meet certain criteria.

I want the calculation to show how many of these fields meet certain criteria and the criteria are different for each field. In some cases, I'm looking for a value of "Yes" in the field. In others, I'm looking for a specific value.

The calculation is currently based on the values in 10 other fields in the same record but this could change.

1 "point" is assigned to each "hit". If five of the 10 fields meet the criteria, the calc field would show 5.

What's the best way to do this?

---

P.S. The data table shows which features in our product are being used (Yes / No) and how much certain features are being used (number values).

We want to identify customers who aren't using certain features at all or enough to get the full benefit. The calculation field is basically a Non-Utilization Score. Higher scores are Worse.

• 1. Re: Calculation to count fields meeting defined criteria.

Create a calculation field (which I'll call "Score") with numeric result, defined like this:

Score =

If ( Field A = "Yes", 1, "" ) +

If ( Field B = "MatchValueB", 1, "" ) +

If ( Field C = 42, 1, "" ) +

If ( Field D = "terrible", 1, "" ) +

If ( not IsEmpty ( Field E ), 1, "" ) + ...

• 2. Re: Calculation to count fields meeting defined criteria.

PatternCount("yesyesyesnoyesnoyesno"; "yes") gives 5, so you could use Patterncount (Field1 & Field2 &...FieldN; "Yes").

Generally you can use a let:

Let (

features = Field1 & Field2 & FieldN;       // <--- concat all your fields here

PatternCount(features; "Yes") +

PatternCount(features; "0") +

)

• 3. Re: Calculation to count fields meeting defined criteria.

Thank you.

I'll try this. Seems so simple I'm sure it will work.

• 4. Re: Calculation to count fields meeting defined criteria.

Thanks for reply but not all criteria are Yes or No. Some test for values being above a threshold or other Boolean tests. Richard's approach will work though.

• 5. Re: Calculation to count fields meeting defined criteria.

sccardais wrote:

Thanks for reply but not all criteria are Yes or No. Some test for values being above a threshold or other Boolean tests.

then formulate your initial requirements accordingly.

I want the calculation to show how many of these fields meet certain criteria and the criteria are different for each field. In some cases, I'm looking for a value of "Yes" in the field. In others, I'm looking for a specific value.

Your initial requirement leads to a patterncount Let, as I suggested. If we're talking about thresholds, we're not in Texas anymore.

• 6. Re: Calculation to count fields meeting defined criteria.

Siphus

Note that the P.S. In my original post mentioned the additional criteria. Probably should have included that in the top.

P.S. The data table shows which features in our product are being used (Yes / No) and how much certain features are being used (number values).

• 7. Re: Calculation to count fields meeting defined criteria.

I like shorter form

Sum (

Field A = "Yes" ;

Field B = "MatchValueB" ;

Field C = 42 ;

Field D = "terrible" ;

Field E <> "" ;

... )

• 8. Re: Calculation to count fields meeting defined criteria.

Thanks. Never would have thought of that.