7 Replies Latest reply on Jun 21, 2018 7:41 AM by Chris O'Hollearn

# Complex If Statements

I have been working on a if statement for a while, and I can't figure out where I am going wrong. Basically I am trying to track company divestitures. These depend on the company, region (i.e. EU, USA, etc.), Country, and the product. As an example, here is my equations that I wrote.

IF [Distributor]="Company A"

AND ([Country Jurisdictions]="EEA"

AND
([Country]="France" OR [Country]="Italy" OR
[Country]="Spain" AND [Product]="CDE"

OR ([Country]="Germany" OR [Country]="Hungary"

OR([Country]="Romania" AND [Product]="CDE"OR [Product]="AAA")

OR ([Country]="Greece" and [Product]="BBB")

OR ([Country]="Spain" AND [Product]="CCC"

OR [Product]="DDD" OR [Product]="EEE")

OR ([Product]="FFF" OR [Product]="GGG"

THEN "Company X"

ELSEIF [Distributor]="Company A" AND ([Country Jurisdictions]="US"
AND [Product]="AAA" OR "BBB" OR "CCC"

THEN "Company Y"

END

The goal is to track divestments globally moving from one company to another and updating the data with the new company. Any help you can provide on this would be much appreciated because this has me stumped and I couldn't find another posting that explained this for me. Tableau Version 2018.1 I would post my workbook, but it has some confidential information in it, and it is very large in size.

• ###### 1. Re: Complex If Statements

Hi Chris,

Can you provide us with some mockup data and provide us with the twbx based off that?

Also, what exactly is the issue that you're seeing when you process that calculation?

Thanks,

Mavis

• ###### 2. Re: Complex If Statements

I'm not sure what is going on but, the "Then" statements are in red and the error is invalid syntax. I will work at putting a sample together and add it here as soon as it is complete

• ###### 3. Re: Complex If Statements

Hi Chris,

Is your formula indented in your workbook? This would certainly help identify which things belong together. At a glance, I think you're missing the location of parenthesis or two (and final Product statement for US is missing '[Product]='), but hard to say without knowing your goal. This is my guess based on how I'm reading it:

IF [Distributor]="Company A" AND [Country Jurisdictions]="EEA"

AND

(

(([Country]="France" OR [Country]="Italy" OR [Country]="Spain")

AND [Product]="CDE")

OR

(([Country]="Germany" OR [Country]="Hungary")

OR

([Country]="Romania" AND [Product]="CDE"OR [Product]="AAA")

OR

([Country]="Greece" and [Product]="BBB")

OR

([Country]="Spain" AND

([Product]="CCC" OR [Product]="DDD" OR [Product]="EEE"

OR [Product]="FFF" OR [Product]="GGG"))

)

THEN "Company X"

ELSEIF [Distributor]="Company A" AND [Country Jurisdictions]="US"

AND ([Product]="AAA" OR [Product]="BBB" OR [Product]="CCC")

THEN "Company Y"

END

Result (didn't know what to put in each row, of course, but got it workable):

Hope this helps.

Best,

Bryce

• ###### 4. Re: Complex If Statements

Here is a quick mock up of the file. In my file it is much larger and there are a lot more fields that I removed from this data set. Hopefully this demonstrates the problem I am having. Now I was trying this in Tableau Prep, but I assume that the equations work the same in Tableau as they do in tableau prep. Let me know if you have any questions.
Thanks,

Chris

• ###### 5. Re: Complex If Statements

I think that was due to the final line of [Product]="AAA" or "BBB" or "CCC" instead of writing it out:

[Product]="AAA" OR  [Product]="BBB" OR [Product]="CCC" as well as issues with parenthesis earlier in the equation.

Hope my previous reply helps you out!

• ###### 6. Re: Complex If Statements

Hi Chris,

Yup Bryce is right, all it was was the last bit where you had the or ='BBB' or 'CCC', you need to tell Tableau which field you're referencing. I can see you've done it in other areas of the calculation, just missed that last bit!

The calculation based on your twbx should be:

IF [Distributor Group]="Company A" AND [Country Flexigroup Jurisdictions]="EEA"

AND

(

(([Country]="France" OR [Country]="Italy" OR [Country]="Spain")

AND [Product Group]="CDE")

OR

(([Country]="Germany" OR [Country]="Hungary")

OR

([Country]="Romania" AND [Product Group]="CDE"OR [Product Group]="AAA")

OR

([Country]="Greece" and [Product Group]="BBB")

OR

([Country]="Spain" AND

([Product Group]="CCC" OR [Product Group]="DDD" OR [Product Group]="EEE"

OR [Product Group]="FFF" OR [Product Group]="GGG"))

)

THEN "Company X"

ELSEIF [Distributor Group]="Company A" AND [Country Flexigroup Jurisdictions]="US"

AND ([Product Group]="AAA" OR [Product Group]="BBB" OR [Product Group]="CCC")

THEN "Company Y"

END

Thanks,

Mavis

• ###### 7. Re: Complex If Statements

Wow!! This was great. This was my first time posting something on the Tableau forum, and I have to say. You guys and girls are amazing. Thank you for all your help. I really appreciate it.