Divide by Zero error in my measure object

[Former Member]

Hi,

In one of my measure objects (it aggregates with a sum) in my universe I have a number that I'm dividing by another number to get a percentage. However, sometimes the denominator is zero, so I get a division by zero error from the SQL database engine. To resolve this I tried to put a case statement around it:

CASE WHEN (SUM(GOAL_COUNT) = 0) Then 0 ELSE

( Sum(START_COUNT) )/( Sum(GOAL_COUNT) )

However, now that column only returns 0s when there actually is data there. I've tried doing this:

CASE WHEN (SUM(GOAL_COUNT) <> 0) Then

( Sum(START_COUNT) )/( Sum(GOAL_COUNT) )

But then it just returns a blank for those lines that are zero, and it returns a zero for those lines that should have a percentage. I know that there is a way around this, but I can't figure it out. Someone please help!

[Former Member]

I realized that this was happening because the objects were intigers. This article was a big help: http://stevesmithblog.com/blog/sql-divide-by-zero-error-solved/

Once I converted it to a float, it worked fine.

[Former Member]

I just figured it out. It is because both my denominator and numerator are integers. Once I converted it to a float, it worked perfectly. Here's what I'm using:

Convert(float, Sum(START_COUNT) )/( NULLIF(Sum(GOAL_COUNT),0) )

[former_member142702]

1) What kind of aggregator is your measure defined to use?

2) Have you checked the generated SQL statement directly against the database?

Regards,

Stratos