Try this as a calculated metric. It will locate domains that are also 2 characters long. Modifying the calculation you provided won't work nicely with "." as an email can be John.Smith@here.com.
// Look at last 3 characters of an email for domain. Return only the domain.
FIND(RIGHT(TRIM([EMAIL]),3),".")=1 THEN RIGHT(TRIM([EMAIL]),2)
Basically says to provide the last 3 characters of the email; in this case the most popular domain lengths. Then check to see if a "." exists telling me it is a 2 character domain, in which case give me the last 2 characters. Else give me the last 3 characters.
TRIM is used to make sure there are no leading or trailing spaces prior to making the calculation. Spaces are considered a character and could cause problems.
If you want to keep the "." along with the domain use this
FIND(RIGHT(TRIM([email]),3),".")=1 THEN RIGHT(TRIM([email]),3)
1 of 1 people found this helpful
Thanks this was very helpful. I added a little tweak to account for data sets where domains have mixed capitalization; e.g. gmail.com, GMAIL.com, etc. If you don't convert the output to Lower case then Tableau will see .com, .COM and .Com as unique strings.
See highlight bold text below.
Find (Right (Trim ([Email]),3),".")=1 THEN Right (Trim (Lower ([Email]) ) ,3)
ELSE Right (Trim (Lower ([Email]) ) ,4)
Note: You only need to add the Lower function to the right site of the THEN, and on the ELSE part of the statement. The reason is that those are the output sides of the calc and you only need to put the output into lower case.
Now the challenge is to make this work for 4 character TLD's like .info b/c instead of 2 choices (2 or 3-char domains) now there are 3 choices 4 char, 3 char and 2-char domains.
I'm thinking we'll adjust the calc to return the last 4 chars and look for a "." In this version that means the domain is a 3-char domain, in which case give me the last 3 chars. Then run as a subcalc the calc you have to id 2-char domains. Anything else is a 4-char domain.
If I dig into it I'll post it here.
Good call on the case sensitivity and the 4 character TLD's. Your solution is definitely a bit more robust in addressing those issues.
Thanks for the comment!
Well, I think you guys might have found the solution by now.
What you can do is, you already have a calculated field that gives you something like
If we create another calculated field on this field it is possible to get the desired result. Something like this in the attached workbook!
It should work for all scenarios!
Email.twbx 13.4 KB
Aman: Yep this covers them all and is an elegant solution.
Vang: If you get these updates, consider marking Aman's solution as answering the question.