A simple SUMIF...that I can't figure out. How do I do this?

[Kev77]

Hi everyone. I have a simple problem that I can't figure out in Looker. I am trying to replicate the Excel formula below in green. What is currently happening in Looker is the formula in red. More info below...

I want to showcase an email Unsubscribe Rate. The formula for the Unsubscribe Rate is simply the sum of all unsubscribes divided by the sum of all emails delivered. HOWEVER - there are a few instances in my dataset where our tool's unsubscribe functionality wasn't working therefore there are blanks. I want to ignore these instances so that my unsubscribe rate is accurate. Currently, it is including instances where we have no unsubscribe data. This makes the unsubscribe rate appear lower than it actually is.

Any thoughts on how I can accomplish this? THANK YOU!!!

[jbernal]

Filtered measures are the way that I typically do something similar to SUMIF. Here's the docs on it.

https://cloud.google.com/looker/docs/custom-fields#creating_a_filtered_measure_from_another_measure

[salehaxsid]

Firstly, you will have to create a measure to calculate sum of delivered email, then create a measure to calculate no of unsubscribes. 
For example:
measure: total_email_delivered{

type: sum 

sql: ${emails_delivered}

}

dimension: Adjusted_email_unsubscribes{

type: number

sql: case
        when ${emails_unsubscribes}  is null then 0  
        else ${emails_unsubscribes}
        end;;

}
the above dimension adds 0 to null values so that the results that we get while calculating Unsubscribe rate is accurate.
measure : Unsubsribe_rate{

type: number
sql: ${total_emails_delivered}/sum(${Adjusted_emails_unsubsribe})
value_format_name: percent_2
}
hope this solves the issue.

[Brent_Wiles]

Does this line of code work? I'd expect including sum in this statement to throw an error. If not, cool, I learned something new!

sql: ${total_emails_delivered}/sum(${Adjusted_emails_unsubsribe})

If it does throw an error you can change the adjusted_email_unsubscribes dimension over to a measure (type sum) and accomplish the same thing once you drop sum out of the unsubscribe_rate sql clause.

[salehaxsid]

It does work, also if you want to sum adjusted_email_unsubscribes then you will have to create a separate measure where type will be sum.

[Kev77]

Hello!

Thanks so much for the response. I have a silly question. Where do I input this formula? Do I create a calculated field? You are referring to creating a "measure" which is not something I am familiar with. In my LookerStudio, I have the option to create a field or a parameter. I don't see a measure. Thanks!

[salehaxsid]

Calculated Field will work fine.