how to aggregate an average case statement
Business task: "what percent of this region's sites beat their target signups YTD"? So I want to take the average daily signup for each site within a region and see if it passes its target. If it does pass, it gets a 1, else 0. After this, I then want to aggregate it to the region level. So for example if 5 out of 6 sites passed, then the passing rate would be 83% for the region.
This is my beast mode so far:
(Case
when AVG(`SignUps`) >= AVG(`Target`)
then 1
else 0
End)
The problem is that while this aggregation does work when I have the site column, it doesn't aggregate when I replace the site column with the region column. Assuming this problem is fixed, I then need to take the sum of all the 1's within a region and divide it by the number of stores so I can get a passing rate for a region. So I thought of how to approach this by using the beast mode below but I still have that first issue in the first part so I was wondering on how I should go about this. Thanks!
SUM(DISTINCT Case
when AVG(`SignUps`) >= AVG(`Target`)
then 1
else 0
End)
/
COUNT(DISTINCT `StoreNumber`)
Best Answer
-
You won't be able to aggregate on an aggregate within a beast mode. You'd need to pre-aggregate your data to get the average signups and average targets. You can then use that aggregated data and apply the beast mode to count the number of records which meet that criteria.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
You won't be able to aggregate on an aggregate within a beast mode. You'd need to pre-aggregate your data to get the average signups and average targets. You can then use that aggregated data and apply the beast mode to count the number of records which meet that criteria.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith I see... so would I need to pre-aggregate this in either magic ETL or SQL dataflow?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- 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