2 Replies Latest reply on Apr 26, 2019 8:52 AM by Horacio Guerra

    Customers with purchases in four consecutive periods

    Horacio Guerra

      I am trying to create a calculation to show a count of customers that had a purchases in four consecutive months.  I could use running sum, but that doesn't help with the "consecutive" portion.  Attached is a sample data set.  Any suggestions?

        • 1. Re: Customers with purchases in four consecutive periods
          Hari Ankem

          Does this output look right to you? All the green circles indicate 4 consecutive months where data exists.

          1.png

           

          If yes, then this is what I have done:

           

          1. Created a calculated field for Year-Month with the following formula: DATETRUNC("month",[Mes])

           

          2. Created another calculated field to check for 4 consecutive months of data:

          (

          1

          +IF DATEDIFF("month",LOOKUP(MAX([Year-Month]),-1),LOOKUP(MAX([Year-Month]),0))=1 THEN 1 ELSE 0 END

          +IF DATEDIFF("month",LOOKUP(MAX([Year-Month]),-2),LOOKUP(MAX([Year-Month]),-1))=1 THEN 1 ELSE 0 END

          +IF DATEDIFF("month",LOOKUP(MAX([Year-Month]),-3),LOOKUP(MAX([Year-Month]),-2))=1 THEN 1 ELSE 0 END

          )=4

           

          3. Added the above calculated field to the Color marks and applied the Green color for the True value, and all others are in grey.

           

          Updated workbook is attached. Hope this helps.

          • 2. Re: Customers with purchases in four consecutive periods
            Horacio Guerra

            Hello Harem

             

            I was checking your post and maybe i didn´t explain myself correctly about the output i need to get.

             

            The correct outputs expected are the following:

             

            1) A formula which calculate 4 consecutive months purchases. For example  if a customer made a purchases in January, February, March, April by April this customer should be consider as a consecutive buyer represented by a number 1. If the customer made a purchase in January, other in Febraury, March and May this customer should not be consider as a consecutive buyer.

             

            2) Also it would be a really good help if you could make a bar graph slide with the output data.

             

            I´m uploading an excel file which explain better the output i need if it can help you to make the calculations

             

            Thank very much for your help

             

            Best Regards

             

            Horacio G