6 Replies Latest reply on Dec 2, 2013 10:07 AM by Doug Stanley

    Syntax for using two logical statements together?

    Doug Stanley

      I need help combining the results of two separate calculated fields:

       

      This one excludes Singapore, Hong Kong, and China from a packed bubbles chart when the user selects CATS from the filter:

       

      SUM(IF CONTAINS([Location], "CATS")

      AND [Partner]<> 'Singapore'

      AND [Partner]<> 'Hong Kong'

      AND [Partner]<> 'China, P.R.'

      then [Value]

      End)

       

      Problem: Choosing anything else from the filter but CATS returns no bubbles.

       

      So I wrote this statement:

       

      SUM(IF not CONTAINS([Location], "CATS")

      then [Value]

      End)

       

      Problem: This one works as long as the user does not choose CATS from the filter choices. If the user chooses CATS, no bubbles are drawn.

       

      How could I combine these statements so that they work together? Thanks for any help.

        • 1. Re: Syntax for using two logical statements together?
          Shawn Wallwork

          Hi Doug are you looking for this?

           

          IF

          SUM(IF CONTAINS([Location], "CATS")

          AND [Partner]<> 'Singapore'

          AND [Partner]<> 'Hong Kong'

          AND [Partner]<> 'China, P.R.'

          then [Value]

          ELSEIF SUM(IF not CONTAINS([Location], "CATS")

          then [Value]

          End

          • 2. Re: Syntax for using two logical statements together?
            Doug Stanley

            Hi Shawn. Thanks. I think that very well may be it, but I'm getting an error: "Expected closing parenthesis or comma while parsing argument list for SUM." I've experimented with a few placements, but still get the error.

             

            Also, once it works I could append more ELSEIF statements, right?

             

            Many thanks for the help.

            • 3. Re: Syntax for using two logical statements together?
              Shawn Wallwork

              Sorry about that, try this:

               

              SUM(IF CONTAINS([Location], "CATS")

              AND [Partner]<> 'Singapore'

              AND [Partner]<> 'Hong Kong'

              AND [Partner]<> 'China, P.R.'

              then [Value]

              ELSEIF not CONTAINS([Location], "CATS")

              then [Value]

              End)

               

              Yes as many ELSEIFs as you want. (Though there may be a character limit depending on your data connection.)

               

              --Shawn

              • 4. Re: Syntax for using two logical statements together?
                Doug Stanley

                That's exactly it, thanks!

                 

                One more, if you would, as I have much to learn about syntax, etc.

                 

                I have several other cases to deal with. Here's how (in bold) I tried to

                add to your answer. What did I do wrong? The error is: "Expected closing

                parenthesis for expression starting at character 186":

                 

                SUM(IF CONTAINS([Location], "CATS")

                 

                AND <> 'Singapore'

                 

                AND <> 'Hong Kong'

                 

                AND <> 'China, P.R.'

                 

                then [Value]

                 

                ELSEIF SUM(IF CONTAINS([Location], "CEE")

                AND [Partner]<> 'KAZAKHSTAN'

                AND [Partner]<> 'POLAND'

                AND [Partner]<> 'RUSSIA'

                then [Value]

                 

                ELSEIF not CONTAINS([Location],"CATSH")

                 

                then  [Value]

                 

                End)

                • 5. Re: Syntax for using two logical statements together?
                  Shawn Wallwork

                  Doug the way you've structured this threw me at first, and now you are struggling with it as well. The SUM() is around the whole thing; then you have a basic IF THEN ELSEIF THEN END statement inside that. So if you write it out like this then syntax errors might be easier to pick up:

                   

                  SUM(

                       IF 

                            CONTAINS([Location], "CATS") 

                            AND [Partner]<> 'Singapore'

                            AND [Partner]<> 'Hong Kong'

                            AND [Partner]<> 'China, P.R.'

                            THEN [Value]

                       ELSEIF

                            CONTAINS([Location], "CEE")

                            AND [Partner]<> 'KAZAKHSTAN'

                            AND [Partner]<> 'POLAND'

                            AND [Partner]<> 'RUSSIA'

                            THEN [Value]

                       END

                  )

                   

                  Also search the web for NotePad++ and download the free version. It will help you get the parentheses sorted because it highlights the pairs.

                   

                  --Shawn

                  • 6. Re: Syntax for using two logical statements together?
                    Doug Stanley

                    Thanks, Shawn. I'm think I'm trying to run before I walk with these complex calcs.