Filtering on an Aggregate FIeld

TheBeard · 2023-08-28T15:03:31+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Accepted answers

trafalger

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?

All comments

trafalger

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

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.

trafalger

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

TheBeard

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.