Aggregated and Non Aggregated - Index Calculation

Hello!
I am attempting to create an index out of two beast modes. One is an aggregated measure that returns the percent share of impressions against the total group. The second is a count of assets to capture how many social videos have been published per each social platform. For example, I'll have two columns for TikTok that tells me the x% share of total impressions and % share of posted videos across all our social platforms.
My issue is I'm I try to calculate those measures into an index. Text to SQL doesn't seem to return anything that actually validates or even returns what I'm looking for. I attempted the below calculations. Would anyone know what is wrong with my formula? Also, if this is even possible to calculate in a beast mode rather than integrating in the data flow?
% Impressions Share: IFNULL(sum(LFM_CONTENT_IMPRESSIONS_V8
)/ NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8
)) over (), 0), 0)
% Posts Share: IFNULL(sum(Post Count
)/ NULLIF(SUM(SUM(Post Count
)) over (), 0), 0)
Index calculation I attempted: (
IFNULL(sum(LFM_CONTENT_IMPRESSIONS_V8
)/ NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8
)) over (), 0), 0) +
IFNULL(sum(Post Count
)/ NULLIF(SUM(SUM(Post Count
)) over (), 0), 0)
*100)
Answers
-
What do you mean by "index" exactly? I don't understand how you are using that term.
In regards to the formula you tried, I find that Beast Mode editor doesn't like it when you use non-windowed aggregation functions with windowed aggregation functions. So, you might have to try something like this:
(
IFNULL(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) OVER () / NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) OVER (), 0), 0) +
IFNULL(SUM(SUM(Post Count)) OVER ()/ NULLIF(SUM(SUM(Post Count)) OVER (), 0), 0)
*100)I also haven't messed around with an empty OVER clause, so that could be an issue too but I'm not sure.
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 86 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive