Print Page - RESOLVED-Need to exclude null value in average

Title: RESOLVED-Need to exclude null value in average
Post by: Tseug on 05 Jun 2013 02:03:55 PM

Hello! This is my problem:

([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?

Title: Re: Need to exclude null value in average
Post by: RKMI on 05 Jun 2013 03:02:02 PM

Hi,

Try this,

Code Select

(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

Title: Re: Need to exclude null value in average
Post by: Tseug on 06 Jun 2013 08:21:57 AM

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

Title: Re: Need to exclude null value in average
Post by: blom0344 on 06 Jun 2013 08:39:57 AM

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

Title: Re: RESOLVED-Need to exclude null value in average
Post by: RKMI on 06 Jun 2013 10:43:57 AM

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

Thanks,
RK

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: Tseug on 05 Jun 2013 02:03:55 PM