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!

       

      Dana