Solution: Beast Mode & Pivot Table Aggregation Error

JedP

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

  WHEN Material 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

  WHEN Material Number = '6'

  THEN SUM(SUM(Cases)) FIXED(FILTER DENY Material Number)

  ELSE SUM(SUM(Gallons)) FIXED(FILTER DENY Material 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.

1. 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.
2. 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

    WHEN Material Number = '6' THEN Cases

    ELSE Gallons

  END)) FIXED(BY (CASE WHEN Material Number = '6' THEN 'Cases' ELSE 'Gallons' END) FILTER DENY Material Number)

The new code now provides the proper results and aggregates on the custom Row Group column.

MichelleH

@JedP This is super helpful, thanks for sharing!

abarrie23

Hi @JedP 

This problem is very similar to one i'm having currently. Any ideas on fixing the following case statement below?

CASE
when Term Length (Months) > 0 then Term Length (Months)
else
SUM(SUM(Term length (lookup)) FIXED (BY Document Number))
END

Jones01

@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

abarrie23

Hi @Jones01

I tried it. I'm still showing the same aggregation error. Is the formula below what you were suggesting?

sum(CASE
when Term Length (Months) > 0 then Term Length (Months)
end) else
(SUM(Term length (lookup)) FIXED (BY Document Number))
END

JedP

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))

END

abarrie23

Thanks @JedP. That was helpful. Thanks to you as well @Jones01