Hello,
I have been spinning my wheels trying to figure out how to average numbers based on more than one parameter.
This is what I mean:
If the number is N/A then Average the median for each salary grade which the employee works 100%.
I wrote it like this:
IF([CUPA NUMBER]='N/A') THEN (Average([CUPA Median] for ([ASSIGNMENT_GRADE] and [FULL_TIME_EQUIVALENCY_PCT]=1)))
ELSE Null
But I continue to get errors or if I tweak it one way or another, the report freezes.
If I remove the [FULL_TIME_EQUIVALENCY_PCT]=1 it work but it includes the CUPA Median of people who are not 100% so the data is skewed with lower averages.
Is there a way to average a column based on two expressions (assignment grade and full time equivalency which is equal to 1)?
Thank you for your help!
Alexis Carter
I'm not sure I understand your specific scenario, but if you just want to aggregate values where a certain condition is met, the correct format would be
aggregate-function(
case when condition=met then value
end
for
for level expression
)
So in your case:
average(
case when [FULL_TIME_EQUIVALENCY_PCT]=1 then [CUPA Median] end
for [ASSIGNMENT_GRADE]
)