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.

jaeW_at_Onyx

@MarkSnodgrass you can't sum the text 'marketing'.

@henryfact you can COUNT the text ... but that doesn't make sense. your count would be = to the number of rows in the dataset.

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

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

ELSE 'Sales'

take Mark's beast mode, "LeadCategory" and put it on an axis WITHOUT AGGREGATION.

Then add an aggregate function next to it like

--rowcount
sum(1) 

MarkSnodgrass

@jaeW_at_Onyx I was assuming 'Marketing' was a numeric amount, but maybe I misunderstood what that field represented.

jaeW_at_Onyx

@MarkSnodgrass given the vertical ticks, 'Marketing' , would be interpreted as the text, Marketing, even if there was a column called `Marketing`-- note the tildas, `, .

henryfact

@MarkSnodgrass - yes Marketing is text - is there another way to get the number of event/display/direct?

@jaeW_at_Onyx Thanks for the response. I'm pretty new to domo so I'm not sure what you mean by

take Mark's beast mode, "LeadCategory" and put it on an axis WITHOUT AGGREGATION.

Then add an aggregate function next to it like sum(1). Can you explain?

Thank you!

MarkSnodgrass

@henryfact can you mock up a display of what you want the end result to look like? Can you also take a screenshot of how your Analyzer is currently configured, making sure to show the filter and sorting properties? With this additional information we will better be able to provide you with a proper solution.

henryfact

@MarkSnodgrass

Its a pretty straightforward nested bar chart, This is what I'm looking for, but the 1 and 13 (below) are incorrect results (undercounting).

The x axis is the created date of the record

MarkSnodgrass

Ok. It seems like your case statement is working properly to get the Marketing, SDR, and Sales names for the series. Have your tried just putting Lead Source in your Y-Axis and choosing Count for the Aggregation? See if that works before we go into using a beast mode for the count.

henryfact

@MarkSnodgrass

I'm currently counting off a record ID. The reason being that there are blank lead source records that I'm designating sales in beast mode. If I use Lead Source as the Series, I get the following. I'm trying to group several of the groups into Marketing and SDR, and the blank ones would be sales

MarkSnodgrass

@henryfact so if you use your beast mode that will display Marketing, SDR or Sales and put that in the series and put Record ID in the Y-Axis and choose Count for Aggregation, are you still getting incorrect numbers?