3 Replies Latest reply on Mar 15, 2016 9:47 AM by Patrick Cotter

    Complex IF statements in a calculated field

    Patrick Cotter

      Hi all,

       

      I was hoping I could get some help with building a calculated field. What I'd like to do is create a new measure that checks multiple cells in the same row and returns a value based on the combination of their values. These cells are binary, with a 1 meaning a "yes" and a 0 meaning a "no."

       

      For example, let's say we have a dataset of employees in an office building, the building has 8 floors, the dataset looks something like this:

       

      NameFloor 1
      Floor 2
      Floor 3
      Floor 4
      Floor 5
      Floor 6
      Floor 7
      Floor 8
      Peter11000000
      Sarah00001000
      Jean00000011

       

      Peter and Jean have offices on two floors each. I'd like to be able to build a field that returns a value of "Floor X," where X is the floor the employee works on, with a return of "Multiple Floors" if they work on more than one (though it would be nice to know how to also return a value of "Floor X and X"). The eventual goal is to be able to filter on this field.

       

      Thanks so much!

        • 1. Re: Complex IF statements in a calculated field
          Joe Oppelt

          A QnD way to do that is to add [Floor1]+[floor2]+ ...

           

           

          IF ([Floor1]+[floor2]+ ...)>1 then  "Multiple" else

             if [Floor1] = 1 then "Floor1" elseif

               [Floor2] = 1 then "Floor2" elseif

          ...

             end

          end

          1 of 1 people found this helpful
          • 2. Re: Complex IF statements in a calculated field
            Joe Oppelt

            Getting "Floor1 and Floor2" would be tedious, but you could build a giant nested IF for the various combinations. 

             

            And maybe you could work up something like this:

             

             

            First have a preliminary calc that grabs the first floor, whether or not there is a "Multiple" in the calc I did in my first reply.

             

            Then do this:

             

             

            if [initial pass calc] = "Multiple" then

              (do something here to skip pas the first floor value  in [preliminary calc] and grab the second one)

             

            My first thoughts are that this will still be a tedious calc to type out.  You can tell that I don't want to.  It depends on how badly you want that. 

            1 of 1 people found this helpful
            • 3. Re: Complex IF statements in a calculated field
              Patrick Cotter

              Thanks! That first reply was exactly what I needed. I'm planning on just going with that, but I want to know how to break it down with the more complex answer if I end up needing to at some point.

               

              Thanks again!