how to aggregate nested 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`)
Answers
-
sum(count(distinct case when AVG(
SignUps
) >= AVG(`Target`) thenStoreNumber
end)) fixed (byRegion
)/
sum(count(distinct
StoreNumber
)) FIXED (byRegion
)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 511 Automate
- 114 Apps
- 389 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive