Mismatched aggregation. Custom aggregations can't_contain both aggregated and nonaggregated fields, in any combination

Question & Answer
, ,
1

I have a calculated field as below that calculates the median of some values but I want it to have values only when the parameter type is numerical. In categoricals I want to show Null

median(parseDecimal({Parameter Value}),[Profile,{Expense Name},{Parameter Label}])

I have used the below but I get the mismatch aggregation error. I understand this is because the medial is calculated on dimensions profile, expense name, parameter label but how to overcome this and show values only in numerical types?

ifelse({Parameter Type[Parameters on Evaluated DAs]} = 'Numerical',
    median(parseDecimal({Parameter Value}),[Profile,{Expense Name},{Parameter Label}]),
    NULL)

image
image922×282 25.8 KB

1 Like
2

Hello @pantelis, we may be able to resolve the error by splitting it up into 2 calculations. First, you could write the ifelse statement to check for the Numerical value:

ParamTypeNumerical = ifelse({Parameter Type[Parameters on Evaluated DAs]} = 'Numerical', 1, 0)

Now that we are returning a number instead of a string, we can aggregate it to bypass the error:
Median for Numerical =

ifelse(
    max({ParamTypeNumerical}) = 1, 
    median(parseDecimal({Parameter Value}),[Profile,{Expense Name},{Parameter Label}]),
    NULL
)

I believe that should provide you with the expected output and remove the error. Let me know if you have any questions!

3

Hello @pantelis, since I have not heard back from you with any follow-up questions, I will mark my previous response as the solution. Please let me know if you still need assistance with this topic and I can guide you further. Thank you!