Solution: Beast Mode & Pivot Table Aggregation Error
Use Case:
This chart uses a Beast Mode code for the Rows and Values in a pivot table chart. The results provide an error that is hard to understand without knowing what is causing the issue. Based on the results, we can also see the Cases is not calculating as expected.
The dataset only has 5 rows of data.
The Rows column in the pivot table is using a custom Beast Mode code.
CASE
WHENMaterial Number
= '6' THEN 'Cases'
ELSE 'Gallons'
END
The Values values column in the pivot table is also using a custom Beast Mode code. The CASE statement has multiple FIXED functions which causes the card to aggregate to the dimensions in the card and not the Row Group column as intended.
CASE
WHENMaterial Number
= '6'
THEN SUM(SUM(Cases
)) FIXED(FILTER DENYMaterial Number
)
ELSE SUM(SUM(Gallons
)) FIXED(FILTER DENYMaterial Number
)
END
The error message also had a hint at the same problem. It states that "Multiple results encountered for the same location". When we change the card type to a table card, it appears that the Gallons section is not aggregating up.
Adding the Material Number helps us see that the card is aggregating, and confirms the card is not using the Row Group column to aggregate. This causes the card to aggregate to the dimensions in the dataset, in this case the card is using the Material Number to aggregate to.
To correct the card and display the correct values we need to do 2 things.
- Correct the case statement logic to allow the FIXED function to use the custom Beast Mode as the default aggregation grouping. This is done by wrapping the case statement with the FIXED function instead of having multiple FIXED functions.
- Add the BY condition to the FIXED function to set the aggregation grouping. Now that the same custom grouping is added to the code that grouping, the card will default to this grouping when aggregating.
SUM(SUM(
CASE
WHENMaterial Number
= '6' THENCases
ELSEGallons
END)) FIXED(BY (CASE WHENMaterial Number
= '6' THEN 'Cases' ELSE 'Gallons' END) FILTER DENYMaterial Number
)
The new code now provides the proper results and aggregates on the custom Row Group column.
Comments
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive