Aggregation Issues with Beast Mode Calculation

Cara · 2024-05-01T17:18:35+00:00

There was an error rendering this rich post.

Cara

Hello, I'm new to Domo/formula writing and I'm running into issues on multiple fronts with a formula I've created.

Original objective: calculate a forecasted value based on stage, weight, and commit. Something like this:

If I include commit as a row, then it will sum correctly:

However, if I exclude commit as a row, then I get the error Multiple results encountered for same location in Pivot Table:

I've tried adding different combinations of FIXED and FILTERED based on reading this article, but I'm still getting the same problem (or the formula doesn't work at all).

https://community-forums.domo.com/main/discussion/61695/solution-beast-mode-pivot-table-aggregation-error

While I can add commit and collapse the rows, it doesn't solve the error which is impacting other visualizations. Does anyone have any suggestions on how to fix this issue? Thank you in advance!

Quick Links

Accepted answers

JedP

Hello @Cara ,

Great question, this is due to the aggregation rollup on the card and the dimension grouping used on the card. The **** error indicates that the grouping in your pivot table doesnt match the grouping in another part of your visual, in this case its your Beast Mode code.

A common way this happens is when you reference a column in your Beast Mode code that is not used on the card itself. Here is a good example of how it happens:

This Sample Beast Mode code uses the Item Color column, but that column is not used in the card. The Beast Mode code creates Item Color groups, and the card doesn't know what to do with those group because they were not used on the card.

CASE

WHEN `Item Color` = 'Blue' THEN SUM(`Order Quantity`)

WHEN `Item Color` = 'Black' THEN SUM(`Order Quantity`)

WHEN `Item Color` = 'Red' THEN SUM(`Order Quantity`)

ELSE 0

END

In the below screen shot we can see the error due to the Item Color grouping in the BM code above.

When you add in the Item Color column, you can now see the why there were multiple groups, the Beast mode code has groups split out causing separate aggregations instead of a rollup aggregation.

To fix this issue, you want to correct your Beast Mode grouping or adjust your columns on the Pivot table to reflect the groups created in the Beast Mode.

Here is a good example of a corrected Beast Mode code that corrects the grouping & Pivot table.

SUM(CASE
WHEN Item Color = 'BLUE' THEN Order Quantity
WHEN Item Color = 'Black' THEN Order Quantity
WHEN Item Color = 'Red' THEN Order Quantity
ELSE 0
END)

The results of the new Beast mode code aggregates the data correctly for the grouping rollup:

All comments

JedP

Hello @Cara ,

A common way this happens is when you reference a column in your Beast Mode code that is not used on the card itself. Here is a good example of how it happens:

CASE

WHEN `Item Color` = 'Blue' THEN SUM(`Order Quantity`)

WHEN `Item Color` = 'Black' THEN SUM(`Order Quantity`)

WHEN `Item Color` = 'Red' THEN SUM(`Order Quantity`)

ELSE 0

END

In the below screen shot we can see the error due to the Item Color grouping in the BM code above.

When you add in the Item Color column, you can now see the why there were multiple groups, the Beast mode code has groups split out causing separate aggregations instead of a rollup aggregation.

To fix this issue, you want to correct your Beast Mode grouping or adjust your columns on the Pivot table to reflect the groups created in the Beast Mode.

Here is a good example of a corrected Beast Mode code that corrects the grouping & Pivot table.

SUM(CASE
WHEN Item Color = 'BLUE' THEN Order Quantity
WHEN Item Color = 'Black' THEN Order Quantity
WHEN Item Color = 'Red' THEN Order Quantity
ELSE 0
END)

The results of the new Beast mode code aggregates the data correctly for the grouping rollup:

david_cunningham

@Cara, the answer provided by @JedP is excellent and thorough. I'd just like to add on one bit that I think is helpful.

It's very important to think about where you're applying the aggregation context with case statements, and the order of operations when processing the beast mode.

case when Department = 'Finance' and MONTH(ScheduleDate) = 2 then SUM(TransactionNumber) end

is different from

SUM(case when Department = 'Finance' and MONTH(ScheduleDate) = 2 then TransactionNumber end)

In the first example, the case statement is evaluated after the aggregation takes place.

In the second, the aggregation takes place after the case statement has been evaluated.

These 2 can yield vastly different results depending on how you have your card configured.

Cara

Thank you both so much! It helped with my little test example. I will work on the larger version next.

other categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.