5 Replies Latest reply on May 12, 2014 10:55 AM by althea.chia

    How do I assign a Max Purchase Date to a Customer in Sales data

      Message was edited by: Dana Komar  

      I am trying to use RAWSQL_DATE to return the MaxNewDate for each CustomerID which I will then use to create the Old/New designation described below, but I can't get it to work properly. My syntax is:  


      RAWSQL_DATE('SELECT Max([NewDate]) FROM [Test Table] GROUPBY [CustomerID]')  


      Is this possible to do???


      I have a set of sales data where customers have purchased items at various venues. I want to create two fields based on their past purchase history to categorize each customer. The resulting fields will be used as row headings in my final table and a distinct customer ID count will be my value field.


      1. One that will say "Old" if a customer's first purchase for all purchases (minimum purchase date) in the data set is before 1/1/2011 and "New" if it is on or after 1/1/2011.


      2. One that will say "Old" if a customers first purchase for a specific venue city is before 1/1/2011 and "New" if it is on or after 1/1/2011.


      For instance, if this was the customer information:


      CustomerID     New Date           VenueCity   

      1234               12/13/2010         New York City

      1234               1/10/2011           New York City

      1234                2/15/2011          Los Angeles

      1234                4/1/2011            Los Angeles


      I would want this result in the underlying data:


      CustomerID     New Date           VenueCity            Field 1           Field 2     Revenue

      1234               12/13/2010         New York City       Old                Old          10

      1234               1/10/2011           New York City       Old                Old          20

      1234                2/15/2011          Los Angeles          Old                New          5

      1234                4/1/2011            Los Angeles          Old                New         10



      The final tables would then have this information:

      Row       Columns

      Field1     DistinctCustomerIDCount  RevenueSum

      New        0                                        0

        Old         1                                        45


      Rows                               Columns

      VenueCity     Field2           DistinctCustomerIDCount  RevenueSum

      New York City                            

                          New               0                                          0 

                          Old                1                                          30  

      Los Angeles

                          New               1                                          15

                           Old                0                                           0


      I have also attached the file I am using. I am very new to Tableau so I am sorry if this is a simple answer.


      Thank you so much in advance!