7 Replies Latest reply on Mar 3, 2017 12:13 PM by Tom W

    How to identify someone has multiple records?

    baron li

      The data table looks like this

       

      Email          Opportunity Number

         A                          1

        A                           2

        B                          1

        C                           1

        C                          2

        C                           3

       

       

      Some of the emails only have one record (opportunity number =1) while others have multiple records

       

      I want to choose those who have multiple records and go back to check their data of their first records (e.g. I choose A and C but go back to check their data when their opportunity number=1)

        • 1. Re: How to identify someone has multiple records?
          Tom W

          Create a calculated field like; COUNT([Email])>1

          Then drag that to the filter shelf and set it to true.

          1 of 1 people found this helpful
          • 2. Re: How to identify someone has multiple records?
            baron li

            Thanks Tom -- I just change the table a little bit

             

            Email          Opportunity Number  Year

               A                          1                 2014

              A                           2                  2015

              B                          1                   2015

              C                           1                  2015

              C                          2                   2016

              C                           3                   2017

             

            The rule is: one email can only occur once in one year

             

            In this way, I want to choose all the new email in one year (saying 2015, new email means opportunity number=1 then I want to look how many new emails have opportunity number 2 in 2016?

            • 3. Re: How to identify someone has multiple records?
              Tom W

              I don't follow what you need.

              One one hand you say "one email can only occur once in a year". If this is the case, do COUNT([Email] + "_" + [Year]) > 1

              Then you go on to explain further which is really don't understand. If you're going to prepare a sample table, please prepare a sample output table which aligns to that. Like "Here's my inputs", and "here's how I want it to output".

               

              It's also really helpful if you upload a sample in Tableau and attach it here as a Tableau Packaged Workbook.

              1 of 1 people found this helpful
              • 4. Re: How to identify someone has multiple records?
                baron li

                Sorry the Tableau Workbook is so  big so just put one table from my database as one example (input)

                 

                 

                1. Each email can have multiple opportunity number but must be in different years

                 

                What I want to do:

                1. Choose the email with opportunity number=1 in 2015 (meaning new opportunities) then (count)

                2. Go to see what's the percentage of them becoming opportunity number 2 in 2016

                • 5. Re: How to identify someone has multiple records?
                  Tom W

                  Please create a smaller version of your workbook and attach it. There isn't

                  much I can do with a screenshot of data.

                  • 6. Re: How to identify someone has multiple records?
                    baron li

                    Thanks I just add one simple file in my first post

                    • 7. Re: How to identify someone has multiple records?
                      Tom W

                      There's a few ways you could do this, I've chosen to do it using Level of Detail calculations.

                      I noticed your workbook is created in an older version, I'm using Tableau 10.1

                       

                      Create a field called FirstYearOpp1 as

                      {FIXED [Email]:

                      MIN(IF [Opportunity Number]=1 then YEAR([Opportunity Start Date]) END )

                      }

                       

                      Create a field called FirstYearOpp2 as

                      {FIXED [Email]:

                      MIN(IF [Opportunity Number]=2 and YEAR([Opportunity Start Date])>[FirstYearOpp1] then YEAR([Opportunity Start Date]) END )

                      }

                       

                      Finally, create a field to calculate the difference between the first two fields;

                      If isnull([FirstYearOpp1]) = true or isnull([FirstYearOpp2]) = true then 'False'

                      ELSEIF [FirstYearOpp2]-[FirstYearOpp1]=1 then 'True'

                      ELSE

                      'False'

                      end

                       

                      Drop this in as a filter and you're all set.