Incompatible aggregation. How to solve?

Question & Answer
, , , ,
1

Hi guys,

I need to create a calculated field that calculates the value for me considering the current month and another calculated field considering the previous month according to the date filtered by the user (filter by parameter).

ifelse(dateDiff({my_date},${Param_Date},"MM") = 0, {my_dynamic_calculated_field}, 0) -- this mount

ifelse(dateDiff({my_date},${Param_Date},"MM") = 1, {my_dynamic_calculated_field}, 0) --previous month

But I don’t need to create the above fields because I get the error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination” Error for calculated field”

Below is how the fields used were created.

--my_dynamic_calculated_field
ifelse(
    ${Param_Product} = 'IM', {my_calculated field_IM},
    ${Param_Product} = 'CS', {my_calculated field_CS},
    0
)

--my_calculated field_IM
round (
    (
        ifelse(isNull({cc_A}), 0, {cc_A}) + 
        ifelse(isNull({cc_U}), 0, {cc_U}) +
        ifelse(isNull({cc_P}), 0, {cc_P}) 
    ) 
    /
    (
        ifelse(isNull({cc_A}), 0, 1) +
        ifelse(isNull({cc_U}), 0, 1) +
        ifelse(isNull({cc_P}), 0, 1)
    )
,0)

Follow the example of how “cc_A” was created:

--cc_A
round(
    (
        ({cc_qty_A} - {cc_qty_A_2})
        /
        sumIf({qty_customers}, sgt= 'A' AND {product} = 'C')

    )
    * 100
,0)

Well, I need to create the first two calculated fields mentioned here.

How can I create these fields?

Thank you so much,
July.

2

Hi

Please review this document which might clarify how you can update this function to have matching aggregations in both numerator and denominator.

1 Like
3

Hello @Ramon_Lopez

The error changed from “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination” to “Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed”

–before:

ifelse(dateDiff({my_date},${Param_Date},"MM") = 0, {my_dynamic_calculated_field}, 0)

–after
sum(ifelse(dateDiff({my_date},${Param_Date},“MM”) = 0, {my_dynamic_calculated_field}, 0))
or
sum(ifelse(dateDiff({my_date},${Param_Date},“MM”) = 0, sum({my_dynamic_calculated_field}), 0))

4

Hi @July,
It’s been awhile since last communication on this thread so following up to see if you’re still encountering issues with your calculated field or if you were able to find a work around in the interim.

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

Hi @July,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new pos tin the community and link this discussion for relevant information if needed.

Thank you!