Filtering on an Aggregate FIeld
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
-
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?
0
Answers
-
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.
0 -
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.
0 -
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?
0 -
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.
0
Categories
- All Categories
- 1.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 560 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 575 Beast Mode
- 13 App Studio
- 28 Variables
- 582 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 26 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 356 Distribute
- 95 Domo Everywhere
- 259 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 89 Community Announcements
- 4.8K Archive