Multiple conditions using case statements but not getting correct results

henryfact
henryfact Member
in Magic ETL

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!

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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)
    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MarkSnodgrass
    MarkSnodgrass Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • henryfact
    henryfact Member

    @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
    MarkSnodgrass Coach

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • henryfact
    henryfact Member

    @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


    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • henryfact
    henryfact Member

    @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


    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach
    edited September 2021

    @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?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories