4 Replies Latest reply on Sep 18, 2018 2:06 AM by Saoud Moco

    Splitting a dimension without creating multiple rows

    Saoud Moco

      Hi

       

      I'm trying to view investment data and trying to rank Investors by number of deals. However, the data source I have can list several investors in the same round (investor names are separated by semi-colons in the same dimension unit).

      Any tips for how I can count the investment round for each investor that contributed to that round ?

       

      Thanks

      S

       

      p.s. using Tableau Desktop 2018.1 on Mac OS X 10.12

      Screen Shot 2018-09-17 at 16.28.14.png

        • 1. Re: Splitting a dimension without creating multiple rows
          Ken Flerlage

          You might be able to create a calculated field that counts the number of semicolons, then aggregate. If you could share your data or a packaged workbook, we could show the actual syntax.

          • 2. Re: Splitting a dimension without creating multiple rows
            Saoud Moco

            Hi Ken

             

            Please find below a sample. As you can see in the "investors" column, I can have a value with more than one name e.g. "BZPlan; Fir Capital". My objective is to be able to count this row as one round of investment for each of the two investors.

             

            Thanks

            S

             

            p.s. I tried Splitting the Investors column but I can only get the first split to count - can't think of a straightforward way to count the other occurrences of the same name in the next Split-n columns (like "Undisclosed Investors")

            --

               

            roundround_dateamountinvestors
            Seed VC - II14/03/20170.6NDC Innovation Fund;Travel Capitalist Ventures
            Seed18/02/20160.11Boost VC
            Seed03/05/20180.26FINEP;Rede de Investidores Anjo de Santa Catarina
            Seed02/02/20180.5Undisclosed Investors
            Seed28/09/20160.12Techstars
            Secondary Market17/07/2018Banco Bradesco;Launchpad Accelerator;Verus Group
            Grant08/06/20180.8Google.org
            Seed19/12/20170.3BrazilLAB;Undisclosed Investors
            Seed VC07/05/20180.56BZPlan;Fir Capital
            Grant29/08/20160.35LaunchVic
            • 3. Re: Splitting a dimension without creating multiple rows
              Ken Flerlage

              OK, I see. You can create a calculated field to count the number of semicolons as follows:

               

              Investor Count

              // Calculate the length of the string without ; then subtract...

              // ...from the length of the original string.

              LEN([Investors])-LEN(REPLACE([Investors],";",""))+1

               

              See attached workbook.

              • 4. Re: Splitting a dimension without creating multiple rows
                Saoud Moco

                Hi Ken

                 

                Thank you for the tip above. It does give me a count of investors per round, which will be useful to see the distribution of how many investors usually go into a Series A or B for instance, by region.

                Apologies if my initial question wasn't clear as to what I was trying to achieve. The idea is to aggregate per investor, all the rounds they've been involved in. That means, if the investor was a sole investor in let's say, 50 rounds - I get the count from Split 1. But if this same investor is a co-investor is other rounds with other investors (and thus appears in the subsequent splits - 2+) then I can't count the rounds this investor has been in with others using the above method.

                Example of Breega Capital below - in effect, they've been involved in two rounds of investment (two different companies) but I can only count one in each split.

                 

                Screen Shot 2018-09-18 at 10.04.50.png

                 

                Maybe I'm approaching it the wrong way as well. Any thoughts most welcome

                 

                Many thanks

                S