8 Replies Latest reply on Jan 18, 2019 5:30 AM by Clint Guillory

    Creating a Filter Using Sets

    Ellen Pfeiffer


      I have a special group of customers that belong to a special group of sales people. The only way I know that these customers belong to this special group is from a list of Customer IDs that the sales people gave me. I created sets for each sales person using the Customer IDs to identify these special groups.


      I'm creating a dashboard where I want to analyze these special groups in comparison to everyone else. I want to create a filter where I can select which set I want to look at. For example, "Daniel's Customers" or "Steve's Customers" vs. everyone else.


      I tried creating a parameter with each special group and then using CASE statement and IF/THEN statements to make a filter, but it says I can't use Boolean Type for these.


      Is there any way I can create a filter where I can select which set I want to look at in the dashboard?



        • 1. Re: Creating a Filter Using Sets
          Tracy Rodgers

          Hi Ellen,


          Sorry for the delay in receiving an answer. You can do this by create each set. Then, create a calculated field for each set that is similar to the following: ([B and C] is the set in the example)


          if [B and C]=True then "B and C" end


          Then, create a parameter that names the above calculation and create yet another calculation to reference the parameter:


          case [choose]

          when "B and C " then [B and C ]

          when "R thru T " then [R thru T ]



          Then, place the field that the above sets were created from on the view. Place the above calculation on the filter shelf and Exclude Null.


          Hope this helps!



          3 of 3 people found this helpful
          • 2. Re: Creating a Filter Using Sets
            Ryan Chase

            Hey @EllenPfeiffer


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


            Assuming that you've already created the sets- I'm calling them [Daniel's Customers] and [Steve's Customers], all you need to due is create a single calculated field like this:


            If [Daniel's Customers] = true then "Daniel's Customers"

            Elseif [Steve's Customers] = true then "Steve's Customers"

            else "everybody else"



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




            -Ryan (send some love- accept my post as the answer!)

            1 of 1 people found this helpful
            • 3. Re: Creating a Filter Using Sets
              Steve Palley

              hi Tracy--


              I tried implementing this solution with 4 sets (which in my case are different sets of countries: All Geos, Top 4 Markets, United States and Rest of World) without any luck.


              I made the sets, then the calc fields referring to the sets, then the parameter referring to the calc fields, and finally another calc field with the CASE statement referring to the parameter. The problem is that I'm only getting the FIRST of the sets as an option from the filter selector. The other three do not appear.


              Any ideas?





              • 4. Re: Creating a Filter Using Sets

                For those of you google searching, you may find this solution helpful. The issue is very similar. The solution utilizes sets instead of groups


                • 5. Re: Creating a Filter Using Sets
                  Elias Jimenez

                  Hi Ryan,


                  Thank you for the guidance and I found it a very simple approach to use.  It worked for the most part but I have a scenario in which it does not provide the complete list of customers.  I've created below a Dimension called "Region" which references various created Sets. Each Set includes a list of customers.  When a customer falls into two buckets (eg. "East" and "Fed"), it will not include the customer if it were included in a previous line.   In the example below, the customer will appear in the "East" Set output but would not appear in the "Fed" output despite the customer being defined in both Sets.  If I flip the order below whereby Fed was the second line and East the third, it would appear in the Fed but not in East.  What should be the embedded logic in the calculated Dimension field to pull all customers in each of the sets below regardless of whether a customer is in another set?






                  If [Central] = true then "Central"

                  Elseif [East] = true then "East"

                  Elseif [Fed] = true then "Fed"

                  Elseif [West] = true then "West"

                  Elseif [South] = true then "South"


                  • 6. Re: Creating a Filter Using Sets
                    Paul Auen

                    I was able to get this working with 2 sets, a parameter, and a calculated field.


                    Here is the calculated field that is used as the filter:


                    IF [Parameter1] = 'Set1' AND [Set1] = TRUE THEN 'Show'

                    ELSEIF [Parameter1] = 'Set2' AND [Set2] = TRUE THEN 'Show'

                    ELSE 'Hide' END


                    This solves the problem that Elias is having with the same item in both sets.

                    This eliminates the need to create a calculated field for every set.


                    This solution might not help with the original question which included an "everything else" option.

                    1 of 1 people found this helpful
                    • 7. Re: Creating a Filter Using Sets

                      Really helpful Paul Auen, thanks!


                      A solution to the 'everything else' option is to just make a set called "All" which includes all members, and to add "All" in the parameter.

                      The calculated field that one uses as a filter then becomes:


                      IF [Parameter1] = 'Set1' AND [Set1] = TRUE THEN 'Show'

                      ELSEIF [Parameter1] = 'Set2' AND [Set2] = TRUE THEN 'Show'

                      ELSEIF [Parameter1] = 'All' AND [SetAll] = TRUE THEN 'Show'

                      ELSE 'Hide' END

                      2 of 2 people found this helpful
                      • 8. Re: Creating a Filter Using Sets
                        Clint Guillory

                        This was a perfect simplistic way of accomplishing what I needed.