Filter out happy people

froghunter
froghunter Member
edited August 27 in Charting

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

Tagged:

Best Answer

  • JasonAltenburg
    JasonAltenburg Coach
    edited August 29 Answer ✓

    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!

Answers

  • Data_Devon
    Data_Devon Member

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

    image.png

    Is this what you were looking for?

  • froghunter
    froghunter Member

    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:

    image.png

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

  • Data_Devon
    Data_Devon Member 
    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
    froghunter Member

    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
    Data_Devon Member

    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
    froghunter Member
    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
    JasonAltenburg Coach
    edited August 29 Answer ✓

    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!

Categories