Creating a calculated "score" based on multiple dimensions

I’m trying to create a score based on various dimensions. Say my database contains information on different job applicants. I want to create a score for each applicant based on the following criteria (all of this is just an example of the type of thing I’m trying to do so don’t worry too much about the details):

–If the dimension Languages Spoken contains “Spanish”, +2. If it contains “French”, +1. else, +0.
–If the dimension Years of Experience >5, +5. If between 3 and 4, +3. else, +0.
–If the dimension Recommended by HR is equal to “Yes”, +2. else, +0.

So for example, someone who speaks French, has 3 years of experience, and is not recommended by HR would have a score of 4. Someone who speaks both Spanish and French, has 1 year of experience, and is recommended by HR would have a score of 5.

How would I go about this type of thing? I’m pretty new to Looker and am mostly just looking to be pointed in the right direction in terms of whether what I’m trying to do isn’t possible or whether it would be done using a calculated dimension, a calculated measure, a table calculation, etc., so that I can start learning more about the correct thing. Thanks so much for any advice!

SELECT
((CASE WHEN languages_spoken LIKE '%Spanish%' THEN 2 ELSE 0 END) + 
(CASE WHEN languages_spoken LIKE '%French%' THEN 1 ELSE 0 END) + 
(CASE WHEN years_of_experience >= 5 THEN 5
WHEN years_of_experience BETWEEN 3 AND 4 THEN 3 ELSE 0 END) + 
(CASE WHEN hr_recommended = yes THEN 2 ELSE 0 END)) 
AS score

I think this would work but I’m not able to test at the moment. I’d add this as a custom measure rather than a table calculation, as the syntax would be different and it wouldn’t be stored outside of the explore you create it in.

1 Like