4 Replies Latest reply on Oct 9, 2015 6:44 PM by Ryan Chase

    Why can't I create a case statement using Sets?

    Kevin Denman

      I wrote the following case statement so the user can alternate between sets. I am getting the error 'Cannot use boolean type in 'CASE' expression'

       

      Are Set's always boolean? Is there a way around this?

       

      Thanks!

       

       

      CASE [Base Filter]

       

      WHEN 'Include Base' THEN [BaseExcluded] <-Set 1

      WHEN 'Exclude Base' THEN [BaseIncluded] <-Set 2

       

      END

        • 2. Re: Why can't I create a case statement using Sets?
          Joshua Milligan

          Kevin,

           

          The expression [Set Name] is a boolean that is true when the record belongs to (is IN) a set and false when the record does not belong to (is OUT of) a set. 

           

          The calculation:

           

          ( [Base Filter] == 'Include Base' AND BaseExcluded]  )

          OR

          ([Base Filter] == 'Exclude Base' AND [BaseIncluded] )

           

          Would give you True for records IN the set based on the parameter selection.

           

          Regards,

          Joshua

          2 of 2 people found this helpful
          • 3. Re: Why can't I create a case statement using Sets?
            Kevin Denman

            Thanks, Joshua.

             

            I ended up doing the following:

             

            1. Created two sets
              1. BaseExcluded
              2. BaseIncluded
            2. Created two calculated fields
              1. Calc_BaseExclude: IF [BaseExcluded] = FALSE THEN "Exclude Base" END
              2. Calc_BaseInclude: IF [BaseIncluded] = TRUE THEN "Include Base" END
            3. Then a third calculated field to add to the filters shelf that uses a parameter ([Base Filter]) so the user can choose to either 'Include Base' or 'Exclude Base'

                      CASE [Base Filter]

                           WHEN 'Include Base' THEN [Calc_BaseInclude]

                           WHEN 'Exclude Base' THEN [Calc_BaseExclude]

                      END


            Adding the third calculated field to the filters shelf allows users to use the parameter to filter out the base or not.


            Do you think this approach is ideal? Or, is there a better way?

            • 4. Re: Why can't I create a case statement using Sets?
              Ryan Chase

              @KevinDenman

               

              I was facing the same issue earlier today, but the solution is actually quite simple.

               

              Basically, when create a set, it creates an invisible column in your data that's boolean- it's values are either true or false based on if that record (or row) is part of your set or not.  Therefore, to accomplish your goal and get the ability to filter by the set, it's easier to just bypass all the parameter business and do the following:

               

              Assuming that you've already created the sets- I'm calling them [set1] and [set2], all you need to do is create a single calculated field like this:

               

              If [set1] = true then "set 1 label"

              Elseif [set2] = true then "set2 label"

              else "everything else"

              END

               

              Now, just put this calculated field onto the filter shelf or right click on it and "show quickfilter".  When you select the different labels from the dropdown it will only show records that belong in the associated set.

               

              Viola!

               

              -Ryan (feel free to accept my post as the answer if it solves your issue! thanks!)