What's wrong with my Beast Mode Calculation?
When I tried to show the results from the queries below I get this error, "An issue has occurred during processing. We are unable to complete the request at this time"
AVG(sum(distinct`Users`)/ `Content ID`)
AVG(SUM(DISTINCT `Users`) over (order by `Content ID`))
I want the average of users over content id, but the results won't show.
... this is bad sql. while you can 'make it work'. it will be prone to invisible errors.
you can't SUM DISTINCT values. ... or at least... you shouldn't. you can COUNT(DISTINCT) values. I assume you're using sum(distinct) b/c you have multiple rows with the same value repeated. your hope is that if you sum(distinct) you'll remove duplicates. but if you have the same number of users for one content ID you'd get the 'wrong value'.
to get this to work, you MUST have content ID on an axis or in the order by clause. once you do that, you probably won't get the desired result b/c you'll have the sum( users) for each content id. which will be ONE VALUE. so when you then try to take the average of one value, you'll get the same number.
I go into it in depth here. https://www.youtube.com/watch?v=eifSYZIcPzg
but the two major action items are, restructure your data so you're not trying to do a sum(distinct) and 2 if you need to know the average across content_ID you may have to preaggregate your data before pulling it into analyzer.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Ahhhh I see. Thanks so much for your help, I really appreciate it!0
- 7.7K All Categories
- 5 Connect
- 921 Connectors
- 244 Workbench
- 479 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 49 Visualize
- 202 Beast Mode
- 2K Charting
- 8 Variables
- 21 Cards, Dashboards, Stories
- 2 Automate
- 349 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 37 Product Ideas
- 1.1K Ideas Exchange
- 2 Community Forums
- 15 Getting Started
- 2 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 579 日本支部