8 Replies Latest reply on Aug 6, 2018 2:48 AM by meenu choudhary

Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Hello

Let's focus on original dimensions and measures below

Dimensions: [Order Date] and [Customer Name]

Measures: [Sales]

I would like to create new calculated field called [IS NEW] which have following logic

If [Customer Name] purchased in 2017

then "EXISTING"

else "NEW"

END

In other word, I wonder that who have ever purchased things in prior year before. That one would be considered as old customer or "EXISTING"

Meanwhile, "NEW" customer is the one who have the 1st purchase just in 2018 (New customer)

Please kindly give me a suggestion to convert my conversation logic into calculated logic congruence with Tableau.

You may also revised my attach workbook and upload to me.

• 1. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Hi Saharat,

You can try below logic:

calculate the min order date for each customer, and if min order date =2017 then it will be new customers.

1. Min order date : {FIXED [Customer Name]:min([Order Date])}

2. if YEAR([Min order date]) <2017 then "existing" else "new" END

2 of 2 people found this helpful
• 2. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Thanks Meenu Choudhary.

1. how to calculate the 1st time customer who has made transactions and rest of the months to be ignored of that customer, if he made any transactions ?

2. calculate the total of new customer per month basis ?

3. calculate running total of every month ?

• 3. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Hi Sam,

1. didn't got the question. If you are trying to get the first transaction of customer month/year wise than below is the logic:

DATE({FIXED [Customer Name]:min(DATETRUNC('month', [Order Date]))})

2. total of new customer per month basis :

Calculate :

new cust per month : { FIXED  [Customer Name]:MAX(if [flag]="new" then 1 else 0 end)}

Drag "Min order date" variable to rows and change it to "Month" discrete and  "new cust per month" to  columns .

3. running total of every month :

with scenario 2 only add quick table calculation " running total"

Apologies as i am not able to attach workbook (no attachments are allowed from office )

1 of 1 people found this helpful
• 4. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Hi SAM M,

Regards,

Kanthesh

1 of 1 people found this helpful
• 5. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Thank you to the 1st comment as correct answer and thank rest comments as useful knowledge.

• 6. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Thank you so much for all your great advice Meenu Choudhary.  It worked well.

• 7. Re: Create new calculated field to identify [Customer Name] whether his/her is "NEW" or "EXISTING"

Thank you kanthesh ! workbook is very useful.