5 Replies Latest reply on Apr 10, 2017 7:18 AM by Josh Delekta

    Case Statement with Multiple Conditions or If Statements

    Josh Delekta

      Hello,

       

      I have a case statement that I use for custom approval order. The case statement functions well currently and I drop it into the filter pane and select "AVG" and choose the correct number. This allows me to equally distribute the visualization across the axis. The statement is essentially this:

       

      CASE [Business Unit]

      WHEN "Business Unit 1" THEN

          CASE [Current Reviewer]

          WHEN "Person 1" THEN 1

          WHEN "Person 2" THEN 2

          WHEN "Person 3" THEN 3

          ELSE 100 END

      WHEN "Business Unit 2" THEN

          CASE [Current Reviewer]

          WHEN "Person 1" THEN 1

          WHEN "Person 2" THEN 2

          WHEN "Person 3" THEN 3

          ELSE 100 END

      ELSE 100 END

      END

       

      I'm now trying to add another dimension into it. The IF Statement would be something like:

       

      If [Business Unit] is "Business Unit 1" AND [Work Area] is "Work Area 1" THEN

          CASE [Current Reviewer]

          WHEN "Person 1" THEN 1

          WHEN "Person 2" THEN 2

          WHEN "Person 3" THEN 3

          ELSE 100 END

      IF [Business Unit] is "Business Unit 2" AND [Work Area] is "Work Area 1" THEN

          CASE [Current Reviewer]

          WHEN "Person 1" THEN 1

          WHEN "Person 2" THEN 2

          WHEN "Person 3" THEN 3

          ELSE 100 END

       

      I tried this:

       

      IF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 1" THEN 1

      ELSEIF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 2" THEN 2

      ELSEIF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 3" THEN 3

      ELSE 100

      END

       

      This obviously worked for me until I tried to add a second Busisuness unit and it only showed the first. Is what I'm trying to do possible with a case statement? Please let me know your thoughts and ideas. I appreciate it. Thank you.

        • 1. Re: Case Statement with Multiple Conditions or If Statements
          Jim Dehner

          Hi Josh

           

          I have a couple questions first did you try

           

           

          IF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 1" THEN 1

          ELSEIF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 2" THEN 2

          ELSEIF [Business Unit] = "Business Unit 1" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 3" THEN 3

          elseif [Business Unit] = "Business Unit 2" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 1" THEN 1

          ELSEIF [Business Unit] = "Business Unit 2" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 2" THEN 2

          ELSEIF [Business Unit] = "Business Unit 2" AND [Work Area] = "Compliance" AND [Current Reviewer] = "Person 3" THEN 3

          ELSE 100

          END

           

           

          or did you try

           

           

          If [Business Unit] is "Business Unit 1" AND [Work Area] is "Work Area 1" THEN

              CASE [Current Reviewer]

              WHEN "Person 1" THEN 1

              WHEN "Person 2" THEN 2

              WHEN "Person 3" THEN 3

              ELSE 100 END

          Elseif [Business Unit] is "Business Unit 2" AND [Work Area] is "Work Area 1" THEN

              CASE [Current Reviewer]

              WHEN "Person 1" THEN 1

              WHEN "Person 2" THEN 2

              WHEN "Person 3" THEN 3

              ELSE 100 END

           

           

          Let me know if either of these helped

           

          Jim

          • 2. Re: Case Statement with Multiple Conditions or If Statements
            Josh Delekta

            Jim,

             

            Thank you for the quickly reply. Neither worked. I suspect it has to do with how I'm formatting the dashboard. I'm going to try to put together a sample workbook for advice. Thank you.

            • 3. Re: Case Statement with Multiple Conditions or If Statements
              Jim Dehner

              I'll gladly look at  that

              Jim

              • 4. Re: Case Statement with Multiple Conditions or If Statements
                Glenn Kuly

                Hi Josh, if I'm following the logic correctly, then this might do it (using a string calculated field w/ Superstore data):

                 

                IF ([Segment] = "Consumer" AND  [Category] = "Furniture" And [State] = "Alabama")
                OR ([Segment] = "Corporate" AND  [Category] = "Furniture" And [State] = "Alabama") THEN "1"

                 

                ELSEIF ([Segment] = "Consumer" AND  [Category] = "Furniture" And [State] = "Arizona")
                OR ([Segment] = "Corporate" AND  [Category] = "Furniture" And [State] = "Arizona") THEN "2"

                 

                ELSEIF ([Segment] = "Consumer" AND  [Category] = "Furniture" And [State] = "Arkansas")
                OR ([Segment] = "Corporate" AND  [Category] = "Furniture" And [State] = "Arkansas") THEN "3"

                 

                ELSE "100"
                END

                 

                Untitled.png

                 

                -- Glenn

                1 of 1 people found this helpful
                • 5. Re: Case Statement with Multiple Conditions or If Statements
                  Josh Delekta

                  Jim/Glenn,

                   

                  Thank you for your responses. I appreciate you looking into this. Please see attached. I've created a custom order based on the actual approval routes but I need to incorporate two things:

                   

                  1) The ability to add "Work Area Level 2" to the calculation

                  2) Populate approvers with 0 invoices in their queue into the route with equal sizing on the chart

                   

                  There might not be any good examples of approvers in different "Work Area Level 2" but they do exist in my approval chains in the real information. Simplistically put, the same approvers are in different orders depending on "Business Unit" and "Work Area Level 2". Any thoughts?

                  1 of 1 people found this helpful