Good morning. Not sure if it's the case statement itself, i.e. incorrect syntax or if there's an issue related to divide a COUNT by a SUM value. We've also tried to run this as COUNT / COUNT but to no avail.
We'd like to use only certain agents when trying to determine a Completed Conversion % for offers. The beast mode currently used to determine COMPANY WIDE conversion rate is:
CASE
WHEN SUM(`Settled`) = 0 THEN 0
ELSE (SUM(`Completed`)/ SUM(`Settled`))
END
However, when determining the conversion rate for specific agents we'd need to isolate them using the OWNER column, which ties in their name with the COMPLETED column from the beast mode above, but we've come up with this but again it's not validating/working:
CASE (
WHEN COUNT(`Owner` = 'Jane Doe', 'John Doe')
THEN 1 ELSE 0 / SUM(`Settled`)
END)
** we've tried several variations on this as well, even using a single agent name, with no luck in getting it to validate/work