Imp 7 - Division error

[bcking]

I have created a report that shows Sales, Quantity and Margin.

Margin is created by Sales / Qty. I have totals at a couple of groupings, so Margin is the Total(Sales) / Total(Qty).

In each Margin calculation I have used an if then statement.
If Total(Qty <> 0) then Total(Sales) / Total(Qty) else (0)

This works for for a few companies (there is a prompt and filter on comapny) but there are a number it doesn't work for. I get the error;

Error Number - 51
DMS-E-MATHEXCEPTION, An arithmetic exception was detected.
EXPENG-E-ZERO_DIVR, <Floating point> divide/mod by zero is invalid.

I'm querying against a SQL Server 2000 DB.

I can't see why the If-then statement would fail.

Any suggestions? Can I use a detailed log to identify the column or data that is failing?

 

[bcking]

I found the solution.

To overcome the problem I needed to have 2 if-then statements.

If (Total(Qty) <> 0 then Total(Sales) else 1) / (If Total(Qty) <> 0 then Total(Qty) else 0)

The reason for the error in the first place was that the DB actually can perform the Sales/Qty calc. before the if-then statement is executed. When it is broken into 2 statements Cognos is forced to do both calculations.

Seems a bit illogical to me, why have an if-then statement if it is ignored?

 

[griffindm]

bcking,

Actually the best way to do this is via a single if-then-else, but rearrange the statement, as in:

Total(Sales) / if(Total(Qty) <> 0) then(Total(Qty)) else Null) ...

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[nagrajm]

Yet another way would be to say

If (Total(Qty)<>0) then (Total(Sales)/Total(Qty)) else (0)

One has to use any of the above methods to find out if Impromptu is able to generate an efficient SQL. Sometimes if the Total(Sales) or Total(Qty) is a calculation involving summary calculations with compound conditions, some of the above suggestions including mine would fail or behave inefficiently. The reason is that Impromptu will try and convert every If..Then..Else statement to Decode(), if it can, and sometime fails.