RESOLVED-Need to exclude null value in average

[Tseug]

Hello!  This is my problem: 

Code Select Expand

([Q1]+[Q2]+[Q3]+[Q4]+[Q5]+[Q6]+[Q7]+[Q8]+[Q9]+[Q10]+[Q11]+[Q12])/[AvgDiv1-12]

It's a survey and each [Q] is an answer.  All of the [Q] fields can hold a numeric value 0 - 10.  I'm trying to find the average answer for each row in a list.  If any of the fields are null, the calculation breaks.

How do I ignore the null values in the calculation?

[RKMI]

Hi,

Try this,

Code Select Expand

(nvl( [Q1],0)+nvl( [Q2],0)+nvl( [Q3],0)+nvl( [Q4],0)+nvl( [Q5],0)+nvl( [Q6],0)+nvl( [Q7],0)+nvl( [Q8],0)+nvl( [Q9],0)+nvl( [Q10],0)+nvl( [Q11],0)+nvl( [Q12],0))/nvl( [AvgDiv1-12],0)

Thanks,
RK

[Tseug]

Thank you!  :)  I ended up using coalesce instead because Cognos didn't like nvl for some reason.   ::)

[blom0344]

nvl is Oracle syntax;  consider using the ANSI compliant  'coalesce' instead..

[RKMI]

Cool, yeah coalesce works too. Sorry, I natrually think in Orcale syntax.  :D

Thanks,
RK