2 Replies Latest reply on Apr 12, 2017 7:15 AM by Josh Delekta

    Case Statement with Multiple Conditions?

    Josh Delekta

      Hello,

       

      Please see attached. 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.

       

      What i really need to do is 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

       

      Thank you.

        • 1. Re: Case Statement with Multiple Conditions?
          Brian Dudley

          You can nest your CASE statement inside an IF statement:

           

          IF [Business Unit] = "BU 1" And [Work Area Level 2] = "Area 1"

          Then

              CASE [Current Reviewer]

              WHEN "Shary" THEN 1

              WHEN "Shaunna" THEN 2

              WHEN "Melissa" THEN 3

              WHEN "Tucker" THEN 4

              WHEN "Adam" THEN 5

              ELSE 100 END

          ELSEIF [Business Unit] = "BU 1" And [Work Area Level 2] = "Area 2"

          Then

              CASE [Current Reviewer]

              WHEN "Shary" THEN 1

              WHEN "Shaunna" THEN 2

              WHEN "Bart" THEN 3

              WHEN "Adam" THEN 4

              ELSE 100 END

          Else 100

          END

           

          I can't really figure out all the tricks you are using to get your presentation -- still learning, myself.

           

          I'm wondering if you might use a dedicated data source with your name to order mapping on it to make it easier to maintain. That might also help with the problem with reviewers with no invoices.

          1 of 1 people found this helpful
          • 2. Re: Case Statement with Multiple Conditions?
            Josh Delekta

            Brian,

             

            Thanks for following up. I actually found that my sort was wrong and I was able apply what I needed at the filter level. I appreciate the help.