1 Reply Latest reply on Sep 27, 2018 9:18 AM by Joe Oppelt

    Calc to Identify New Customers

    Andres Galban

      Hi all, im having some difficulty implementing the following logic -

      If the customer has 2 prior months of no sales, label the current month (new calc field) as a returning customer.

       

      In this case Purchase and No Purchase are different measures

       

         

      CustomerMonthPurchaseNo Purchase
      1231/1/20181
      4561/1/20181
      7891/1/20181
      1232/1/20181
      4562/1/20181
      7892/1/20181
      1233/1/20181
      4563/1/20181
      7893/1/20181
      1234/1/20181
      4564/1/20181
      7894/1/2018 1
      1235/1/20181
      4565/1/20181
      7895/1/20181
      1236/1/20181
      4566/1/20181
      7896/1/20181
        • 1. Re: Calc to Identify New Customers
          Joe Oppelt

          Depends on how your sheet, table and data are set up.


          Can you attaché a workbook with sample data that simulates your application?

           

          I would probably keep a rolling status for each customer that maintains the last purchase date.  Then on each current row, if the last date was more than 2 months ago then identify him at that time as new (or whatever you need to do there.)

           

          If you have rows for each customer on each month (and the sample data you gave DOES indicate you do that) then a window_sum(sum([Purchase],-2,0) would indicate that he had a prior purchase in the previous 2 months if the current month is 1 and the window_sum is >1.

          1 of 1 people found this helpful