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

    Complex If Statements

    Chris O'Hollearn

      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"

      AND [Active Ready Mix]="CDE" or [Active Ready Mix]="FGH")

      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.

      Thanks in advance.

        • 1. Re: Complex If Statements
          Mavis Liu

          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
            Chris O'Hollearn

            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
              Bryce Larsen

              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")

                      AND ([Active Ready Mix]="CDE" or [Active Ready Mix]="FGH"))

                  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
                Chris O'Hollearn

                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
                  Bryce Larsen

                  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
                    Mavis Liu

                    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")

                     

                     

                            AND ([Active Ready Mix]="CDE" or [Active Ready Mix]="FGH"))

                     

                     

                        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
                      Chris O'Hollearn

                      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.