Mismatched aggregation - Help Please

Q&A
, , , , ,
1

Im trying to create an if statement that says if {Operational Score L4} is less than the percentile, then populate “Meets”, otherwise populate “above”

The formula i’m using is below.

ifelse({Operational Score L4}<{Percentile},“Meets”,“Above”)

Below is the error im seeing:

Mismatched aggregation. Custom aggregations can’t contain both aggregate “PERCENTILE” and non-aggregated fields “PERCENTILE_DISC(75) WITHIN GROUP (ORDER BY “127d3ff5-3263-47ec-880d-fbf58478870c”)”, in any combination.

if it matters, the fields referenced are calculated fields (below)

{Percentile} = percentile({Operational Score L4},75)

{Operational Score L4} = {Ambiguity L4 Score}+{Complexity L4 Score}+{Execution L4 Score}

Ambiguity score, complexity score, and execution scores are all calculated fields that involve ifelse (purpose is to change the text to an integer)

2

Hi @akring,

Your condition is being evaluated at the row level but {Percentile} is being calculated by grouping rows. To fix it, use the percentileOver function instead of the percentile function.

If you use the correct partition and use PRE_AGG with percentileOver, it will give you the same result as the percentile function without grouping your rows.