4 Replies Latest reply on Jan 30, 2017 8:58 PM by Shinichiro Murakami

    Excel Matrix to Tableau

    Hasan Mohammad
    Hasan Mohammad Jan 29, 2017 4:29 PM

      Hello,

       

      I have a project tracker that's maintained in Excel that I'd like to build in Tableau. Each row represents a project, and each column represents an attribute of the project that we're tracking. There are over 20 columns/attributes in the Excel file. It looks something like this:

       

      The values in the columns are either going to be 1's or 0's

       

      I want to build something in Tableau like this, where each project having a column value of 1 is a shaded circle, and not showing anything when the value is zero:

       

                                                           

      I tried playing around with this in Tableau but couldn't get it looking the way I wanted. How would I create something like this?

      • 827 Views
      • Tags:
        • 1. Re: Excel Matrix to Tableau
          lei.chen.0
          lei.chen.0 Ambassador Jan 29, 2017 5:38 PM (in response to Hasan Mohammad)

          Hello Hasan,

           

          My solution is to use measure values for color mark, and set the color for zero to white (the same as the background) to make it "invisible"

           

          Please refer to the attached workbook (10.1.3) for details.

           

           

          Regards

           

          Lei

          • 2. Re: Excel Matrix to Tableau
            Joe Oppelt
            Joe Oppelt Ambassador Jan 29, 2017 5:48 PM (in response to Hasan Mohammad)

            Attached is a spreadsheet like the one you described.


            Also attached is a V8.2 workbook (it will upgrade to anything you are on.)  It uses that spreadsheet.


            Sheet 1 is a simple sheet of the data in the spreadsheet.


            In Sheet 2 I changed the format to be circles, and I put "Measure values" onto COLORS.  I made the color gradient to be only two steps.  One for zero and one for 1.  I selected white for the color of zero and green for the color of the value 1.  I chose white so that the zero-value circles simply don't show up on the white background.

             

            The other way to do this would be to NULL out the value of zero in a calc, but you'll need a separate calc for each measure.  Tableau won't place a mark at all if the value is null, so you wouldn't have to mess with colors, but you might have 50 actual measures, and that could get cumbersome to maintain.

            • 3. Re: Excel Matrix to Tableau
              Hasan Mohammad
              Hasan Mohammad Jan 30, 2017 8:30 PM (in response to Hasan Mohammad)

              Hi Lei Chen / Joe Oppelt

               

              Thanks for providing those examples - that was exactly what I was looking for!

               

              I'm running into another issue now. I checked the source file I need to use, and there are around 100 columns that need to be shown, similar to the example I provided earlier. How do I get to show all the columns using Measure Names/Measure Values?

               

              I looked through the forums and found a workaround here: Re: Maximum number of columns displayed?

               

              This allows me to show each measure if I drag them all up to Columns, but that doesn't display the way I want it.

               

              How would I get it working the way you both have demonstrated, but with 100 columns?

              • 4. Re: Excel Matrix to Tableau
                Shinichiro Murakami
                Shinichiro Murakami Ambassador Jan 30, 2017 8:58 PM (in response to Hasan Mohammad)

                You can use data pivot provided your version is >9.0

                 

                From  edit data source.

                 

                Select all 100 columns of years except dimensions "project' in this case.

                 

                 

                Pivot them. (100 is east to pivot)

                 

                Then follow the instruction already provided.

                (not sure that's same as below or not)

                 

                 

                 

                 

                 

                Thanks,

                Shin