cognos report studio multiple columns average

Question

Im relatively new to cognos so please bear this in mind.

I wanted to create a report in report studio where i have 3 measures ( A ,B , C ) as columns and a 3rd calculated column as the average of these columns.

However , when using the average function , i cannot add multiple inputs .

i tried the arithmetic alternative ( a+b+c)/3 because this will not handle cases when values are null

Thank you in advance

Answer 1

If nulls are not zero for average

case when 
    not (a is null and b is null and c is null)
then
    (coalesce(a,0) + coalesce(b,0) + coalesce(c,0)) /
    (case when a is not null then 1 else 0 end +
     case when b is not null then 1 else 0 end +
     case when c is not null then 1 else 0 end)
end

Answer 2

I would create a value count expression to count the non-null columns:

Value Count

CASE WHEN [A] is not null THEN 1 ELSE 0
+
CASE WHEN [B] is not null THEN 1 ELSE 0
+
CASE WHEN [C] is not null THEN 1 ELSE 0

Then you can use this new data item as the dividend for your average calculation:

Average

(coalesce([A],0) + coalesce([B],0) + coalesce([C],0))/[Value Count]

If division by 0 is a problem, you can wrap the average expression in another CASE to return null when [Value Count] is 0:

CASE 
WHEN [Value Count] > 0 THEN (coalesce([A],0) + coalesce([B],0) + coalesce([C],0))/[Value Count] 
ELSE null 
END