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.2K Product Ideas
- 1.2K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 437 Beast Mode
- 23 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive