4 Replies Latest reply on May 26, 2016 1:09 PM by Ivan Young

    How to filter one column while selecting a different column

    Cameron White

      Hi,

       

      I have data that is structured similar to the table below. What I am looking to do is have a quick filter based on the customer where they select the customer they want and it filters out the projects that do not have that customer. So if they select Customer 1, all records for Project 1 and 3 are displayed. If they select customer 6, only all records for Project 2 are displayed. Is there a known technique to do this?

       

       

      ProjectSiteCustomer
      Project 1Site 1Customer 1
      Project 1Site 1Customer 2
      Project 1Site 1Customer 3
      Project 1Site 2Customer 4
      Project 1Site 2Customer 5
      Project 2Site 3Customer 6
      Project 2Site 3Customer 7
      Project 2Site 4Customer 8
      Project 2Site 4Customer 9
      Project 2Site 4Customer 10
      Project 3Site 5Customer 11
      Project 3Site 5Customer 12
      Project 3Site 5Customer 13
      Project 3Site 5Customer 14
      Project 3Site 5Customer 1
        • 1. Re: How to filter one column while selecting a different column
          Sanjay Nimbark

          Hi,

           

          You can use only relevant item on filter select.

          Let me if this help , best way if you can put the workbook then I can give you modified version and help you that way too.

           

          Regards,

          Sanjay

          • 2. Re: How to filter one column while selecting a different column
            Cameron White

            That allows me to get only relevant values for the quick filter but what I need is to see all customers in the quick filter but filter the view by the projects that the customer I select is a part of.

             

            If my data was structured this way, it would not be an issue:

             

            ProjectSiteCustomerCustomer 1?
            Project 1Site 1Customer 1Yes
            Project 1Site 1Customer 2Yes
            Project 1Site 1Customer 3Yes
            Project 1Site 2Customer 4Yes
            Project 1Site 2Customer 5Yes
            Project 2Site 3Customer 6No
            Project 2Site 3Customer 7No
            Project 2Site 4Customer 8No
            Project 2Site 4Customer 9No
            Project 2Site 4Customer 10No
            Project 3Site 5Customer 11Yes
            Project 3Site 5Customer 12Yes
            Project 3Site 5Customer 13Yes
            Project 3Site 5Customer 14Yes
            Project 3Site 5Customer 1Yes

             

            My actual data has thousands of customers though so I do not want to have thousands of columns.

            • 3. Re: How to filter one column while selecting a different column
              Cameron White

              I found a solution. I  created a custom SQL query to simulate the fourth column in this table:

               

              ProjectSiteCustomerCustomer 1?
              Project 1Site 1Customer 1Yes
              Project 1Site 1Customer 2Yes
              Project 1Site 1Customer 3Yes
              Project 1Site 2Customer 4Yes
              Project 1Site 2Customer 5Yes
              Project 2Site 3Customer 6No
              Project 2Site 3Customer 7No
              Project 2Site 4Customer 8No
              Project 2Site 4Customer 9No
              Project 2Site 4Customer 10No
              Project 3Site 5Customer 11Yes
              Project 3Site 5Customer 12Yes
              Project 3Site 5Customer 13Yes
              Project 3Site 5Customer 14Yes
              Project 3Site 5Customer 1Yes

               

               

              It would be great to find a solution that does not require custom SQL though.

              1 of 1 people found this helpful
              • 4. Re: How to filter one column while selecting a different column
                Ivan Young

                Hi Cameron,

                I believe you can achieve your desired result with a parameter, but keep in mind parameters do not update automatically.

                 

                1. Build a parameter from your customers field.

                 

                2. Build your calculated filter.

                WINDOW_SUM(SUM((IF [Project]= ((IF [Customer] = [Customer Parameter] THEN [Project] END))

                THEN 1 ELSE 0 END))) > 0

                 

                3.  Layout your fields.  You will have to add customer to detail if you don't want it displayed in the view.

                 

                4.  Add the calculated field from step 2 to filters and select True.

                 

                5.  Adjust the compute by on the filter.  Probably table down.

                 

                Let me know if you have any questions.

                 

                Good luck,
                Ivan