2 Replies Latest reply on Aug 11, 2016 12:24 AM by Melissa Black

    Multiple dimensions into a group for filtering with duplicate fields from one dimension

    Melissa Black
    Melissa Black Aug 10, 2016 7:36 AM

      Hi,

       

      I am struggling to create a filter for my data - I have tried calculated fields, parameters and sets and nothing does exactly what I want to do.

       

      I have a data set that I would like to filter based on different conditions of multiple dimensions but I also would like certain fields to appear in multiple groups e.g.

       

      The user can select set 1 or set 2 ....  in one filter and get the appropriate data

       

      If

      [Group 2]="A" OR

      [Group 2]="B" OR

      [Group 2]="C" AND [If>3]=1 AND [Category]="A" OR

      [Group 2]="C" AND [If>3]=1 AND [Category]="B" OR

      [Group 2]="C" AND [If>3]=1 AND [Category]="C" OR

      [Group 2]="D" AND [If>3]=1 AND [Category]="A" OR

      [Group 2]="D" AND [If>3]=1 AND [Category]="B" OR

      [Group 2]="D" AND [If>3]=1 AND [Category]="C"

      THEN "SET 2"

      END

       

      IF

      [Group 2]="A" OR

      [Group 2]="B" OR

      [Group 2]="C" AND [Category]="A" OR

      [Group 2]="C" AND [Category]="B" OR

      [Group 2]="C" AND [Category]="C" OR

      [Group 2]="D" AND [Category]="A" OR

      [Group 2]="D" AND [Category]="B" OR

      [Group 2]="D" AND [Category]="C"

      THEN "SET 1"

       

      Any help would be greatly appreciated and I have attached a demo workbook of the data.

       

      Thanks

      Mel

      • 1739 Views
      • Categories: Actions & Filters, Calculations
      • Tags:
        • 1. Re: Multiple dimensions into a group for filtering with duplicate fields from one dimension
          Benjamin Greene
          Benjamin Greene Aug 10, 2016 8:24 AM (in response to Melissa Black)

          Hey Melissa, I have attached a workbook with what I believe to be the solution you are looking for. Here's how to replicate it:

           

          1. Edit the Set 1 and Set 2 calculated fields so that they end with ELSE "NULL " (note that the space before closing the quote is intentional)

          2. Create a new calculated field (I called it Combo) that is simply [Set 1]+[Set 2]

          3. Create a String parameter (I called it Select Set) with 3 values. The first value is simply a space, which is displayed as All, and the other two values are SET 1 and SET 2. Right-click the parameter and select "Show Parameter Control." I also like to format the parameter as a single value list.

          4. Create a new calculated field (I called it Filter) whose syntax is

          IF CONTAINS([Combo], [Select Set])

          THEN "SHOW"

          ELSE "HIDE"

          END

          5. Drag this field to the Filter card and filter on "Show"

           

          Now, when you select a value in the parameter control, the Filter calculated field will only show rows whose Combo value contains the selected string. Since all values contain a space somewhere in them, that is why the "All" parameter value is only a space. Let me know if this works for you.

          • 2. Re: Multiple dimensions into a group for filtering with duplicate fields from one dimension
            Melissa Black
            Melissa Black Aug 11, 2016 12:24 AM (in response to Benjamin Greene)

            Thank you very much! I had been struggling with this for a while.

             

            Works perfectly!

             

            Mel