FIXED BY referenced in another beast mode
Hello everyone,
I am trying to reference a beast mode (streak) to another (streak_breaker) that's using a FIXED (BY) function, but it keeps on returning an error. Is this not feasible?
Here's my example data and calculations:
- streak_breaker = MAX(MAX(CASE WHEN
status
= 'draft' THENcampaign_id
END) FIXED (BYaccount_id
)) » Gets the latest campaign that wasn't sent per account - streak = COUNT(CASE WHEN
campaign_id
>dynamic_streak_breaker
THENcampaign_id
END) » Counts the number of campaigns published later than the one that wasn't sent
If only this two works together, I can count the sent streak accurately even if I do not filter anything, which would result to 6, as the streak_breaker
would be 4 and there are 6 campaigns more than 4, or if I filter Sponsor 1, which would result to 5, as the streak_breaker
would switch to null/0
campaign_id | sponsor | status |
---|---|---|
10 | sponsor2 | sent |
9 | sponsor 1 | sent |
8 | sponsor2 | sent |
7 | sponsor 1 | sent |
6 | sponsor2 | sent |
5 | sponsor 1 | sent |
4 | sponsor2 | draft |
3 | sponsor 1 | sent |
2 | sponsor2 | sent |
1 | sponsor 1 | sent |
Actually, even if I changed the streak_breaker to the below formula , the streak beastmode will validate but the chart will still throw an error
MAX(CASE WHEN status
= 'draft' THEN campaign_id
)
Best Answer
-
It’s because you have an aggregate inside an aggregate which isn’t allowed. You could attempt to use a window function so the max is returned for each row depending on your partitions and then do the count on that value
COUNT(CASE WHEN `campaign_id` > MAX(`latest_unsent_campaign_acct`) FIXED (BY `your_partition_field_or_remove_by_clause`) THEN `campaign_id` END)
Window or fixed functions will return a single value for each row which will allow you to then aggregate.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
It’s because you have an aggregate inside an aggregate which isn’t allowed. You could attempt to use a window function so the max is returned for each row depending on your partitions and then do the count on that value
COUNT(CASE WHEN `campaign_id` > MAX(`latest_unsent_campaign_acct`) FIXED (BY `your_partition_field_or_remove_by_clause`) THEN `campaign_id` END)
Window or fixed functions will return a single value for each row which will allow you to then aggregate.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive