Filter out happy people

froghunter

So, that's a strange title… Here's the deal:

We have survey data about support tickets. It's pretty basic— just Good, Neutral, and Bad as responses. A person can be interviewed more than once. So, I've been creating a visual of "Sentiment by user". It's a stacked bar graph with the following:

X axis: The user's name
Y axis: Count of user sentiment
Series: User sentiment

So, with a little formatting and sorting, we end up with Bob on the X axis with a tall blue bar since he's always happy but needs lots of help. Jane has a shorter blue bar with a little red top bar since 1 support ticket went badly but the rest were good. Joe has just a tall red bar since he's never happy. And so on…

So, I'd like to include only the people that have at least one non-happy (that is, neutral or bad) response. This would, using the examples, exclude Bob. How could I do that? Thank you.

Name	Date	Disposition
Bob	8/25/2024	Good
Jane	8/21/2024	Good
Joe	8/22/2024	Bad
Bob	8/10/2024	Good
Bob	8/12/2024	Good
Joe	8/9/2024	Bad
Jane	8/8/2024	Bad
Joe	8/26/2024	Bad
Bob	8/26/2024	Good
Bob	8/16/2024	Good
Joe	8/26/2024	Bad
Jane	8/17/2024	Good
Jane	8/11/2024	Good
Bob	8/14/2024	Good
Bob	8/18/2024	Good

JasonAltenburg

I think this is what you want:

MAX(CASE WHEN `Disposition` IN ('Neutral', 'Bad') THEN 1 ELSE 0 END) OVER (PARTITION BY `Name`)

This formula does the following:

• It checks each user's "Disposition" for "Neutral" or "Bad".
• If found, it returns 1; otherwise, 0.
• The OVER (PARTITION BY 'Name') ensures this check is done per user.

Next Step to filter your data:

• Add this Beast Mode calculation as a filter in your card.
• Set the filter to include only values equal to 1.

COUNT(*) will not validate, even on it's own in a beast mode.

I got help with this answer using my custom GPT, which you can try out here:
Domo Beast Mode Bot

Hope this is helpful!

Data_Devon

I think the native Filter option on Disposition would accomplish this.

Drag your disposition column into the "Filter" area in the Analyzer, then select "Good", and change the filter rule to "Not In"

Voila! You'll only see responses that are NOT "Good".

Is this what you were looking for?

froghunter

That's related, but not quite what I'm looking for. What I would like to do is exclude everyone that is only happy, but for those that remain show all data, including happy responses. So, on a graph like this:

I would like to exclude Bob, but keep everyone else exactly as they are, including the good parts.

Data_Devon

CASE
    WHEN SUM(response = 'Good') = COUNT(*) 
    THEN 'All Good! Omit this person'

ELSE 'Mixed Reviews'

What if you did a Beastmode like this ^ and then filtered to only "Mixed Reviews"

That Beastmode is looking at the Count of "Good" responses, and comparing it to the Count of total responses. If those counts are equal, then 100% of that persons responses are "good" and therefore can be omitted.

If those values are not equal, then they have other responses and therefore would pass through your filtering on this Calculated Field/Beastmode.

I think that'll work. Keep us posted!

froghunter

Thank you. That looks promising. I've updated a little bit for formatting and added END. I'm getting a syntax error, but that may only be a matter of figuring out where it lies, so I'm replacing pieces with simpler elements to find the location.

Here's what I have:

CASE
WHEN SUM(response = 'Good') = COUNT(*) THEN 'All Good! Omit this person'
ELSE 'Mixed Reviews'
END

Data_Devon

I think you'll have to change the "response" to the name of that column, which I think is Disposition.

CASE
WHEN SUM(`Disposition`= 'Good') = COUNT(*) THEN 'All Good! Omit this person'
ELSE 'Mixed Reviews'
END

I might need an expert to chime in here….

froghunter

https://community-forums.domo.com/main/discussion/comment/98024#Comment_98024

I did that in the actual code, but kept it general here. It's actually called definition_display_value because that's so simple and intuitive. :-)

JasonAltenburg

I think this is what you want:

MAX(CASE WHEN `Disposition` IN ('Neutral', 'Bad') THEN 1 ELSE 0 END) OVER (PARTITION BY `Name`)

This formula does the following:

• It checks each user's "Disposition" for "Neutral" or "Bad".
• If found, it returns 1; otherwise, 0.
• The OVER (PARTITION BY 'Name') ensures this check is done per user.

Next Step to filter your data:

• Add this Beast Mode calculation as a filter in your card.
• Set the filter to include only values equal to 1.

COUNT(*) will not validate, even on it's own in a beast mode.

I got help with this answer using my custom GPT, which you can try out here:
Domo Beast Mode Bot

Hope this is helpful!