If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Average based on multiple expression items

Started by acarter13, 23 Feb 2016 06:01:34 PM

Previous topic - Next topic

acarter13

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


navissar

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]
)