6 Replies Latest reply on Nov 2, 2016 6:24 AM by Marc MOUSSA

    Buying in different time frames

    Jonathan Cooper

      Hi everyone

       

      Could anyone help me with a problem.

       

      I need to get a distinct count of buyers that purchased in the last 7 days but not in the previous 90 days.  These are called lost buyers.  I also need a list of the buyers that bought in the last 7 days but not in the previous 120 days, these are called lost buyers.

       

      So I'd like the number of buyers that are lost and the number of buyers that are lapsed.

       

      Does that make sense?  This is my formula.

       

      { FIXED [Buyer Account Number]:

      IF(SUM(IF [Date Sold]>=[Week Ending]-365
          AND [Date Sold]<=[Week Ending]-121
      THEN [Number of Records]
      END)>0)

          AND

      ISNULL(SUM(IF [Date Sold]>=[Week Ending]-120
          AND [Date Sold]<=[Week Ending]-8
      THEN [Number of Records]
      END))

      AND

      SUM(IF [Date Sold]>=[Week Ending]-7 AND [Date Sold]<=[Week Ending]
      THEN [Number of Records]
      END)>0
              THEN "Lost"

       

      ELSEIF

      (SUM(IF [Date Sold]>=[Week Ending]-365
          AND [Date Sold]<=[Week Ending]-91
      THEN [Number of Records]
      END)>0)

          AND

      ISNULL(SUM(IF [Date Sold]>=[Week Ending]-90
          AND [Date Sold]<=[Week Ending]-8
      THEN [Number of Records]
      END))

      AND

      SUM(IF [Date Sold]>=[Week Ending]-7 AND [Date Sold]<=[Week Ending]
      THEN [Number of Records]
      END)>0
              THEN "Lapsed"

                  END}

       

      Any help appreciated