Cannot solve problem: how to FILTER on Subtotals per category

user09492
user09492 Member
in Archive

I have data on payments over 2 yr period.

Payments have categories such as grocerries, car, insurance, pharmacy, etc.etc.

I used default domo vizualizer to visualize total costs(Y-axis),  BY YEAR (X-axis) using STACKED charts.

when I click within a stack, a specific category lights up - NICE.

 

Problem: There are over 100 categories, and many are irrelelvant. I just want to visualize top categories where subtaotal WITHIN category is above specific dollar value (e.g. $1,000). IOW, I want to batch exclude all categories of purchases where subtotal/category is less than $1,000.

 

How do I do that?

 

In attached pic, these irrelevant categories, are displayed as thin lines.

 

When I try to filter by value, and set "min" value of $1,000 the app looks for individual transactions, as opposed to AGGREGATE sums within a category.

 

Comments

  • Chronos
    Chronos Member

    Is anyone able to help out with this request?

  • Thor
    Thor Member

    Hello!

    The best way to achieve this functionality is to write a BeastMode that will allow you to filter out data that is less than your desired dollar amount (in your example, $1,000)

    CASE
    WHEN
    column_name >= 1000 THEN 1
    ELSE 0
    END

    Then you will create a filter that filters by 1, removing all of the values that are not at the minimum threshold.

    It is important to note that if you are SUMing your column in your card, you will also need to SUM the BeastMode (and vice versa).

    e.g.
    SUM(`column_name`) >= 1000 ....

     

    Then you will create a filter that includes rows where your Beast Mode equals 1, filtering out the 0 values (those with a SUM less than 1000)

    Have a great day!

    Regards,

    Josh Thorn
    Technical Advisor
    DOMO Support

Categories