I'm facing a challenge in Oracle Service Cloud Analytics reports while trying to create a column definition expression that combines multiple conditions. I have two distinct expressions for counting records with a non-null email address and a unique phone number.
I am seeking assistance in combining them into a single expression, where I aim to retrieve the count of records with both a unique phone number and a non-null email address.
Here are the individual expressions:
Count of records with email address: sum(if(contacts.email is not null, 1, 0))
Count of records with unique phone number: count(distinct contacts.ph_asst)
I tried the following expression, but it ends up giving me following error when the report is saved:
(sum_distinct(1, if(contacts.email is not null, contacts.ph_asst))) - 1
I would greatly appreciate any insights, suggestions, or corrections and want to verify whether the expression I came up with is correct according to the documentation mentioned below.
The Report Functions are defined in this (documentation) as follow :
sum_distinct(expr, reference) This function returns the sum of distinct values in an expression for a particular record (reference) rather than for all records of the same type in a table. For example, if you want to calculate the sum of all quotas for a particular sales person, you could add a function of sum_distinct(sa_period2accts.quota, sa_period2accts.acct_id).
count(expr) This function returns the number of rows included in the data set expr.
if(expression, then result, else result) This function returns the then result if the expression is true and returns the else result if the expression is false. For example, the expression if(incident.c$field=1, ‘Yes’, ‘No’) returns Yes for incidents where the value of c$field is 1. If the value isn't 1, No is returned.
You can use IS NULL and IS NOT NULL as part of the expression. For example, you could use the expression if(incident.c$field IS NOT NULL, 'Yes', 'No').