4 Replies Latest reply on Aug 29, 2018 4:06 PM by Zach DiSalvo

    Distinct Count of Multiple Dimensions

    Zach  DiSalvo

      Hello All -

       

      I am wanting to get the count of the number of tenants in a spreadsheet that I am working with. Unfortunately I am not able to share a packaged workbook as the data is sensitive so I have included a table below with a sample of data in a similar format

       

         

      PropertyTenantsAddress
      AWendys 1111 5th ave
      BTaco bell, Starbucks1112 7th ave
      CStarbucks1113 9th ave
      DDry Cleaner, Liquor store, Starbucks1114 12th ave
      EMcDonalds, liquor store1115 13th ave

       

      What I want to have is a bubble chart that shows the tenant name and the total count of each tenant name. So there would be a bubble and the counts: Wendys (1), Taco bell (1),  Starbucks (3), dry Cleaner (1), Liquor Store(2), and McDonalds (1). The size of the bubble would be based on the count so Starbucks would have the largest bubble followed by liquor store and then all the others. To separate the tenants I did a custom split in the tenant column by "," so it then added 3 new columns and would now look like the table below

       

            

      PropertyTenantstentants1tentants2tentants3Address
      AWendys Wendys nullnull1111 5th ave
      BTaco bell, StarbucksTaco bellStarbucksnull1112 7th ave
      CStarbucksStarbucksnullnull1113 9th ave
      DDry Cleaner, Liquor store, StarbucksDry CleanerLiquor store Starbucks1114 12th ave
      EMcDonaldsMcDonaldsnullnull1115 13th ave

       

      The problem that I'm having is getting there to only be one bubble per tenant, instead I am getting 3 diffent bubbles per tenant, one for each column. Is there a way to avoid this problem? I'd like to avoid as much data manipulation in excel as possible because this would likely be a spreadsheet that download and re-upload once a month so I would rather use calculated fields in tableua.

       

      Any Ideas??

        • 1. Re: Distinct Count of Multiple Dimensions
          Aaron Dobbins

          This could be a good opportunity to use Tableau Prep to try to manipulate the file and make it row-based data with one tenant per line, which is what you really want to do packed bubbles.  If you can import the original file into Excel and do a split on Tenants column to get the three separate columns, save that as CSV.

           

          Connect to that CSV in Tableau Prep and do a pivot on those Tenant fields to get your row-based data. I did something similar today with a column-based data set.  When you create the pivot in Prep you would use only the split tenant fields in the pivot and the other fields for Property and Address would be replicated properly to each row.  The pivot basically says "take each column you designate in the pivot and create a row with that value, and replicate the rest of the data to the row".

           

          PropertyAddressTenant
          A111 5th AveWendys
          D1114 12th AveDry Cleaner
          D1114 12th AveLiquor Store
          D1114 12th AveStarbucks
          1 of 1 people found this helpful
          • 2. Re: Distinct Count of Multiple Dimensions
            Zach  DiSalvo

            I don't have any experience using Tableau prep. Any other recommendations?

            • 3. Re: Distinct Count of Multiple Dimensions
              Aaron Dobbins

              Not good ones off the top of my head.  I was thinking LOD calcs but it was ugly in my head. With Tableau in general row-based data is much easier to work with.  If you can get it in the correct state, it makes life much easier.

               

              I used Prep for the first time today after watching the 4 minute video on Pivots.  Took about half an hour to get it sorted on a 302 column based file that I needed to make row-based.  It looks and feels a lot like Tableau Desktop if you are using Desktop already.  You can save the flow you create so that if you were updating this dashboard regularly you can load the new file, run the same prep and output the same file.  You can even have Prep create a HYPER or TDE file for you that you can connect to directly in Tableau Desktop.

               

              The Pivot Step

              • 4. Re: Distinct Count of Multiple Dimensions
                Zach  DiSalvo

                It seemed ugly in my head when I was trying to think it through as well. I'll take a look into Tableau prep to see if I can get it figured out. Thanks for the help!