2 Replies Latest reply on Apr 12, 2018 10:00 AM by Joe Oppelt

    Dynamic Repeat Purchase Calculation - n Times

    Amber Muni

      Given the table attached here is an explanation - Of the 500 customers who purchased in Jan, 300 made another purchase in Feb, 100 made another in Mar, and so on. Of the 600 customers who purchased in Feb, 350 made another purchase in Mar, 200 made another in Apr and so on.


      Given a data set with customer IDs and the dates of multiple purchases, Is there a way to create this view in a dynamic way to account for the fact that the data set is changing in terms of customers and length of time quite frequently.


      I've combed the forums but am having trouble. My main issue is that I cant just fix a customer at  the minimum date of first purchase because I want to do this for every purchase they have. Meaning I don’t want to stop at their second purchase but I want to take into account their 3rd, 4th, 5th etc…indefinitely.  Any guidance or ideas would be great!

        • 1. Re: Dynamic Repeat Purchase Calculation - n Times
          Joe Oppelt

          The LOOKUP() function lets you look forward or backward in your table.  So:


          IF NOT  ISNULL(LOOKUP(SUM([Sales]),4)) THEN 1 END


          This would set a value of 1 in the current month for the given mark (customer/month) if that same customer had a value 4 months later.  (You probably would have to mess with the table calc settings to get the right addressing of dimensions so that it looks 4 marks away from the current mark within the same customer ID (rather than looking 4 customers away in the same month.)  This will be specific to your data and sheet setup.)


          You would have 4 of these calcs, each looking a respective 1, 2, 3 and 4 months away.

          Then you would have another calc that would sum up (WINDOW_SUM() ) the 4 LOOKUP values, this time summing within the given month, rather than within the given customer.

          1 of 1 people found this helpful
          • 2. Re: Dynamic Repeat Purchase Calculation - n Times
            Joe Oppelt

            If you need help setting this up, you'll have to upload a sample workbook so I can show you.