4 Replies Latest reply on Jan 12, 2017 4:41 AM by Ferga McGovern

    Multiple Or filters

    Ferga McGovern

      Hi

      My data is in the format of the employee table below.

      I want to get a summary of all employees willing to relocate to each of the cities,

       

      EmployeeFirst Choice
      Location
      Second Choice
      Location
      Third Choice
      Location
      TomNew YorkBostonChicago
      JoeNew YorkChicagoBoston
      MarkSan FranNew York

      Boston

       

       

      I would like to present the data as shown in the city visual below or something similar.

      Ive looked at creating parameters and calculations but have failed miserably!

      Any suggestions or pointers gratefully received.

           

      CityFirst Choice
        Location
      Second Choice LocationThird Choice
        Location
      Total
      Boston0123
      Chicago0112
      New York2103
      San Fran1001
        • 1. Re: Multiple Or filters
          Mahfooj Khan

          Hi Ferga,

           

          A small data formatting required to transpose your columns into rows. Find my approach, I've used custom SQL to transpose your data.

          Custom SQL Query: If your data source is excel then you can use below query.

          SELECT [Employee],[First Choice Location] AS Location, "First Choice Location" AS [Choice] FROM [Sheet1$]

          UNION

          SELECT [Employee],[Second Choice Location] AS Location, "Second Choice Location" AS [Choice] FROM [Sheet1$]

          UNION

          SELECT [Employee],[Third Choice Location] AS Location, "Third Choice Location" AS [Choice] FROM [Sheet1$]

           

          then your view will be like this:

          Now as per below screen shot just drag the fields.

          Workbook version 9.3 attached for your reference. Let me know If this help.

           

          Mahfooj

          2 of 2 people found this helpful
          • 2. Re: Multiple Or filters
            Simon Runc

            hi Ferga,

             

            So the easiest way to do this is to Pivot the data (which we can do on the connect to data screen...if data is .csv or Excel...if it's in SQL the pivot can be done as a view)

             

             

            this then gives the data like this (I've also renamed the pivot fields to Choice and City)

            Once it's like this your table is really simple to build.

             

            Hope that helps.

            1 of 1 people found this helpful
            • 3. Re: Multiple Or filters
              Ferga McGovern

              Thanks Mahfooj Khan, my underlying data is sql db so I went back to the source and edited the view based on your custom sql with location/choice.

              Apologies for the delay in my response, it took me a long time to realise I had to go back to the very start

              thank you for taking the time to answer the query

              • 4. Re: Multiple Or filters
                Ferga McGovern

                Thanks Simon for your answer.  As you suggested I had to go back to my sql source and edit the view. That combined with Mahfooj Khan suggested solution got me what I needed.  Apologies for the delay in my response, it took me a long time to realise I had to go back to the very start

                 

                thank you for taking the time to answer the query