4 Replies Latest reply on Jul 16, 2018 9:25 AM by Jillian Lellis

    New Customers- Can't use Min/Max

    Jillian Lellis

      Hello tableau friends,

      I am about to bang my head against the wall.

      I need to find new customers that have either NEVER purchased prior to 2018, or haven't purchased in 5 years. I couldn't do MIN/MAX because I need to look at the 2nd most recent transaction (and in some instances the third because they could have multiple transactions this year but not in 5 years before that). All I need is a Count of these per rep.

       

      I have created a RANK calculation and then tried a variety of calculations to exclude/include certain customers. I am so close to figuring this out but I'm stuck.

       

      Attached is a sample dataset. Any help is appreciated!
      Thanks,

       

      Jill

        • 1. Re: New Customers- Can't use Min/Max
          Jim Dehner

          Hi

          I think I understand - you want customers that purchased this year for the first time OR customers that purchased this year AND had not purchased for the previous 5

           

          have you tried this

           

           

           

           

          if YEAR(min([First Date])) = 2018 then 'New'

          elseif min([JD LOD date diff])>=5 and max(YEAR([Lastdate]))=2018 then 'New'

          end

           

          where the LOD is this

           

          FIXED [Cuno]:Min(datediff('year',[First Date],today()) ) }

           

           

          now in the data sample I could not find any records that met the second criteria  so it returned null

           

          see the attached

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: New Customers- Can't use Min/Max
            Jillian Lellis

            OK so maybe I wasn't explaining it.

            A "New" Customer is either a customer who's first transaction was this year, or they haven't transacted with us in 5 years and now they're transacting again. I tried the above calculation but it is including those with transaction this year and last year as new--which isn't what I'm trying to get.

             

            The "lastdate" is the date that we need to focus on because the firstdate for those is to eliminate duplicates on rental and lease transactions.

             

            Now if there was a way I could put first, second and third dates in a column rather than a row with null values, i could do a date diff. hmmm

            • 3. Re: New Customers- Can't use Min/Max
              Bryce Larsen

              Hi Jillian Lellis

              I cannot open your workbook as we still use 10.3 in the office, but I came up with something you could look to use:

              My example (twbx attached) uses Superstore Data. I've created a few LOD expressions that you could modify to fit your needs.

               

              Year Reference is just the max year in the data, so feel free to modify this to YEAR(TODAY()) or the like.

              First step in flagging a new customer is straightforward: identify customers whose first order was place this year. So, we need to find their first order date (year, in this case):

              Second step is slightly more complicated: find customers with an order this year, so find last order date year:

              But we only want to flag them if their last order year before this year was N years ago. So we find their last order before this year:

              So to put this all together:

              Line 1 is truly a new customer: first order was placed this year. But second part flags customers who had an order this year and last order before this year was 2 or more years ago (so you could modify this to be 5 instead, but not useful to do with this dataset).

               

              I added a "NewCustomerCD" to sort the "new" customer type to present it more clearly. True new customers appear first here and then 'returning' new customers appear second.

               

              Hopefully this helps!

               

              Bryce

              • 4. Re: New Customers- Can't use Min/Max
                Jillian Lellis

                YOU ARE AMAZING! Thank you so so much!!!