Create calculated field using CASE statement to define new customer

[sweitzercounsel]

I am a small business practice owner trying to build a Looker Studio dashboard that visualizes my CRM data. I want to be able to easily filter on new vs current customers. I define new customers as someone who started working with me in the last 6 months (though may change that later).

It seems I need to make a custom field using a CASE statement where I compare their start date with the current date minus 6 months. If their start date occurred less than 6 months ago, they're defined as 'new'. Else they would be 'current.

Anyone done something similar to this in Looker Studio? Can anyone help?

Thanks in advance!

[HankinsonC]

for "the 180 days" interpretation of "6 months." 

Case when date_dif(today(),start_date) >= 180 then "Existing"
when date_dif(today(),start_date) < 180 then "New"
else "Error" end

You can also do this in months with datetime_diff, if you're ok with the # of days fluctuating a little over time.

Case when datetime_dif(today(),start_date, MONTH) >= 6 then "Existing"
when datetime_dif(today(),start_date, MONTH) < 6 then "New"
else "Error" end

This only works if you have one row per customer and will be more complicated if it's something like one row per transaction. 

[sweitzercounsel · Accepted solution]

Thank you for this! The only change I had to make to get it to work is the function name (datetime_diff - diff with 2 ff's)

Case when datetime_diff(today(),Start Date, MONTH) >= 6 then "Current"
when datetime_diff(today(),Start Date, MONTH) < 6 then "New"
else "Error" end

For context, I manually export appointment data from my CRM/EHR (i'm a therapist), and do a separate export of client/customer data. Then I setup a blend using customer name as the join (the CRM/EHR won't export any kind of numerical UID). Using this, I was able to add a Control in Looker Studio to filter on new vs current clients. Again, thank you!

[HankinsonC]

My apologies for the typo! I'm glad you got it working.