average and for in Cognos Analytics

Question

Presently I'm using the following code to obtain specific average based on conditions -

round(average( [Duration] for [P_CODE], [STEP_TYPE]='Electronics Processing'),2)

Unfortunately, I'm getting an error when we run this code as a part of our environment. I have to find a way to do the same to get the duration's average based on this STEP_TYPE instead of using the "for" in the average for the specific STEP_TYPE.

This Average is getting populated within a List shown below. Thanks

Answer 1

Try this:

round(
     average (
          CASE 
          WHEN [STEP_TYPE] = 'Electronics Processing' THEN [Duration] 
          ELSE null 
          END
     for [P_CODE])
,2)

Nulls are excluded from aggregates such as count() and average().