Aggregation Issues with Beast Mode Calculation

Options
Cara
Cara Member
in Charting

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:

image.png

If I include commit as a row, then it will sum correctly:

image.png

However, if I exclude commit as a row, then I get the error Multiple results encountered for same location in Pivot Table:

image.png

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!

Best Answer

  • JedP
    JedP Domo Employee
    Answer ✓
    Options

    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.

    image.png

    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.

    image.png

    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:

    image.png

Answers

  • JedP
    JedP Domo Employee
    Answer ✓
    Options

    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.

    image.png

    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.

    image.png

    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:

    image.png

  • david_cunningham
    david_cunningham Coach
    Options

    @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! ✔️**

  • Cara
    Cara Member
    Options

    Thank you both so much! It helped with my little test example. I will work on the larger version next.

Categories