Mismatched aggregation issue, how to make it aggregated

Hi, i’m trying to create a calculate field Vs.goal to get a subtract (otp-otp goal), but i got an error said Mismatched aggregation. otp formula is sum({otp_num})/sum({otp_denom}), otp goal formula is ifelse({content_value}=‘Amazon Studios’,99.9,{content_value}=‘HVC’,99.0,{content_value}=‘NonHVC’,94.0,NULL),`
how can I make the calculation happen?

Can you wrap your hard coded values into an avg and take the firstValue of your content value partitioned by the content_value (always return the content value you want)?

opt goal formula =
ifelse(firstValue(
[{content_value}],
[{content_value} ASC],
[
{content_value}
]
)=‘Amazon Studios’,avg(99.9),…)

Hi Max, thanks for the suggestion, I tired it but got an error. Any insight?

Ahh I’m sorry about that.

You don’t need brackets enclosing the first field.

firstValue({content_value},[{content_value} ASC],[{content_value}])

i was able to solve this by using OTP- MIN(OTP GOAL) to make otp goal aggregated, but thank you for your help!

1 Like