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:
Field1 DistinctCustomerIDCount RevenueSum
New 0 0
Old 1 45
VenueCity Field2 DistinctCustomerIDCount RevenueSum
New York City
New 0 0
Old 1 30
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!
Test.twbx.zip 764.8 KB