4 Replies Latest reply on Dec 11, 2017 2:36 PM by Shinichiro Murakami

Write a formula to include multiple / not mutually exclusive conditions

When I tried to write a formula on the Condition column to group all information into three categories,Good, Neutral, and Bad and ensure all three categories include all possible combinations, my formula didn't work out. What I want to see is that all conditions starting with Good including "Good", "Good, Neutral", "Good, Bad","Neutral, Good" etc. fall under the Good category in the formula. Same thing with Neutral and Bad.

However, the current formula as below worked out more like a tunnel down. The end result for the Bad category, my last ELSEIF formula in Tableau contains only Bad, but not "Bad, Neutral" or "Bad, Good". Any ideas on the formula?

IF CONTAINS(UPPER([Condition]), 'Good') THEN 'Good'

ELSEIF CONTAINS(UPPER([Condition]), 'Neutral') THEN 'Neutral'

ELSE 'N/A'

END

• 1. Re: Write a formula to include multiple / not mutually exclusive conditions

Hi A C

Here you go.

FYI

Regards,

Shin

• 2. Re: Write a formula to include multiple / not mutually exclusive conditions

You've fallen into a simple trap with UPPER.

Basically you're checking if "Good" is the same as "GOOD" .... it's not the same because the case of the letters are different.

This is what you should do:

IF CONTAINS(UPPER([Condition]), 'GOOD') THEN 'Good'

ELSEIF CONTAINS(UPPER([Condition]), 'NEUTRAL') THEN 'Neutral'

ELSE 'N/A'

END

Than you're checking the right thing

• 3. Re: Write a formula to include multiple / not mutually exclusive conditions

Hi Shin and Chris McClellan,

Thank you both for your detailed message. I ran into the same problem after I corrected the formula (basically copy your formula). See my attached image. The count number for the Bad category is only 1. In fact, there are 6 Bad in total.That's what I meant: the end results worked out more like a tunnel down. In both your and my formula, we started with "GOOD" so that the good category contains all the Good related combinations. However, it doesn't happen to the Neutral or the bad category.

• 4. Re: Write a formula to include multiple / not mutually exclusive conditions

HI A C

with If then statement, once one condition meet, that filed is excluded from rest of conditions.

So to meet your expectation, respective count needs to be done independently..

Regards,

Shin