6 Replies Latest reply on Aug 8, 2018 4:43 PM by Thomas Tobin

    Repeat Customer on Customer ID & Date

    Thomas Tobin

      Hi,

       

      Firstly sorry to trouble you with a question which I am sure will be simple to many.  I have been trawling through the many questions on repeat customer reporting and none seems to provide the answer I need.

       

      I am using Tableau v10.5.1.  I have data with purchases listed in rows.  Each purchase has an item ID, customer ID and purchase date.

      I need to report the count of purchases in a particular date range (e.g. most recent month) and, also, the count of those purchases made by customers who had purchased anything in the 12 months prior to that purchase.

       

      The second part is causing  me the problems.  I think I need to use an LOD calculation to work this out but can't nail it.  The aim (I think) would be to be to add a calculation ("Repeat") to each row telling me if a purchase had been made by that customer ID in the 12 months before that purchase date.  The Excel formula (in cell D2) would be:

      =IF(COUNTIFS(A:A,A2,C:C,">="&EDATE(C2,-12),C:C,"<"&C2)>0,1,0).

      Data example and desired output below.  Workbook attached.  Thanks in advance,

       

      Tom

       

      Data

      ABCD
      1CUSTIDItemIDPurchase_DateRepeat
      2A123CCF221-Aug-181
      3A123CCUE81-Apr-181
      4A123PPI994-Apr-170
      5A440CCUE84-Jan-180
      6B204D9IOD29-Jul-180
      7B204CX88U1-Apr-170
      8C440CCF2230-Jun-180
      9D333CCUE81-May-180

       

      Desired Output (for period 1-Jan-2018 to 31-Aug-2018):

      ItemIDCount of SalesSum of Repeat
      CCF2221
      CCUE831
      D9IOD10
      Grand Total63