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
-
@abarrie23 did you try my suggestion on your post?
https://community-forums.domo.com/main/discussion/67998/domo-error-warning-multiple-errors-encountered-for-same-location-in-pivot-table-indicated-by#latest
0 -
Hello @abarrie23 ,
I think i see the issue here, it looks like you are mixing your aggregations in your case statement.
The THEN result appears to just be a non aggregated column, while the ELSE results in a Window function aggregation. When you mix aggregation results and non aggregation results you end up with multiple results which is what causes the issue.
To correct the issue, you will need to re-design your code to ensure that the results are all the same level. And it can be hard to say what will work without knowing the data structure, but if i had to guess, you might be able to add an aggregation to your THEN statement to get the aggregations to align. So depending on your datasetup, I would suggest trying a SUM or a MAX or MIN around your THEN statement.
CASE
when Term Length (Months) > 0 then MAX(Term Length (Months))
else
SUM(SUM(Term length (lookup)) FIXED (BY Document Number))
END1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive