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!

Find more posts tagged with

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.