Aggregation Issues with Beast Mode Calculation
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).
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!
Best Answer
-
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
WHENItem Color
= 'BLUE' THENOrder Quantity
WHENItem Color
= 'Black' THENOrder Quantity
WHENItem Color
= 'Red' THENOrder Quantity
ELSE 0
END)The results of the new Beast mode code aggregates the data correctly for the grouping rollup:
2
Answers
-
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
WHENItem Color
= 'BLUE' THENOrder Quantity
WHENItem Color
= 'Black' THENOrder Quantity
WHENItem Color
= 'Red' THENOrder Quantity
ELSE 0
END)The results of the new Beast mode code aggregates the data correctly for the grouping rollup:
2 -
@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.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**2 -
Thank you both so much! It helped with my little test example. I will work on the larger version next.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 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
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive