Multiple conditions using case statements but not getting correct results

henryfact

Hi Domo Community,

In Beast mode, I've been trying to parse out some data for visualization but cannot get domo to display the right numbers. For example, with the data below, domo only returns the entries for one of the lines for Marketing and LeadIQ for SDR - it won't give me the sum of Display, Direct, Event or the sum of Propsecting & Lead IQ. Sales returns the correct number as there are no other conditions.

Formulas always validated per Domo

CASE

WHEN `Lead Source`= 'Display'

OR `Lead Source`= 'Direct'

OR `Lead Source`= 'Event' 

THEN 'Marketing'

WHEN `Lead Source`= 'Prospecting'

OR `Lead Source`='LeadIQ' 

THEN 'SDR'

ELSE 'Sales'

END

I searched the forum and tried using the IN operator as some other posts suggested:

CASE

WHEN `Lead Source` IN ('Event','Display','Direct')

THEN 'Marketing'

WHEN `Lead Source` IN ('LeadIQ','Prospecting')

THEN 'SDR'

ELSE 'Sales'

END

I get the same answer/problem either way. The formula isn't summing up each of the records, only returning one of them (e.g. only returning number of event records vs. counting the number of records in event, display, and direct)

Any guidance would be greatly appreciated! TIA!

MarkSnodgrass

Have you tried wrapping your case statement with a SUM function like this:

SUM(CASE
WHEN `Lead Source` IN ('Event','Display','Direct')
THEN 'Marketing'

WHEN `Lead Source` IN ('LeadIQ','Prospecting')
THEN 'SDR'

ELSE 'Sales'
END)

Also, another reason why you might not be able to sum is if you have things in your sorting properties. That will force it to evaluate differently and affect your results.