Filtering on an Aggregate FIeld

TheBeard
TheBeard Member
in Beast Mode

Hi everyone. I'm a novice at SQL and more specifically, DOMO. This is also my very first post on this forum so forgive me if I've not followed a proper format or given enough specifics upon first request.

I currently have a table card that aggregates a dataset by sum.

For example, there are 4 line items and I have a BEAST MODE calculation like this:

SUM(ActiveQtyPct) OVER (PARTITION BY ChildPN)

This gives me a new field with the sum of the ETL-calculated "ActiveQtyPct", partitioned, without grouping the line items, as I wanted. However, this sum needs to be based on the card filter, so I cannot calculate the SUM in the ETL.

Do you have any ideas of how to filter on this aggregate? How can I make a working card filter for this beast mode calculation? Any suggested work arounds? I've been on this for days and I can usually find creative ways to do these things, but I'm at a loss with this one. And there are still quite a bit of DOMO functions I don't know.

Thanks for any help.

Best Answer

  • trafalger
    trafalger Coach
    Answer ✓

    Do you need the card to respond to page filters/date range? Because you could sum up the ChildPN's active qty in the dataflow and then join it back on and then filter on that? (Which based on your question, I think you do).

    Could you remove the line items from this view, filter the ChildPN here and then have a drill that shows the line items?

Answers

  • trafalger
    trafalger Coach

    What does the card look like? Do you need the partition? If the card is using ChildPN on it you can just do a sum and then then filter on that.

  • TheBeard
    TheBeard Member

    I need the partition because there are multiple ChildPNs in the dataset. The sum needs to reflect a value for each ChildPN.

    Here is a screen capture. The field "PhaseoutPct" is the aggregated field from beast mode.

    image.png

  • trafalger
    trafalger Coach
    Answer ✓

    Do you need the card to respond to page filters/date range? Because you could sum up the ChildPN's active qty in the dataflow and then join it back on and then filter on that? (Which based on your question, I think you do).

    Could you remove the line items from this view, filter the ChildPN here and then have a drill that shows the line items?

  • TheBeard
    TheBeard Member

    I think drilling down is my best option. It appears to function in a similar way as to what I was going for, so thanks for the suggestion and appreciate the help.

Categories