Zero divide error

[PeterL]

I have 2 fields where either can be zero. I need to determine the percentage differance between the two. How can I avoid a zero divide error when one of these fields is zero.

 

[lbass]

You only need to check if the denominator is zero, so try something like the following:

if {table.field1} = 0 then 0 else
{table.field1}-{table.field2} % {table.field1}

-LB

 

[lbass]

Should have added parens:

if {table.field1} = 0 then 0 else
({table.field1}-{table.field2}) % {table.field1}

-LB

 

[PeterL]

The problem is that either field might be zero. I am showing the % change is sales from one year to another. The result might be a plus/minus. If last years sales were 5 and this year was 0 then I would show -100%

 

[kskid]

Just expand on LB's formula

if {table.field1} = 0 then
0
else if {table.field1} > 0 and {table.field2} = 0 then
-100
else
({table.field1}-{table.field2}) % {table.field1}

 

[PeterL]

So close. The following function works except when the YTD is zero and the PYR has a valus. When they both have a value the formula works regardless of which field is greater.

if {AR1_CustomerMaster.SalesYTD} = 0 then
0
else if {AR1_CustomerMaster.SalesPYR} > 0 and {AR1_CustomerMaster.SalesYTD} = 0 then
-100
else
({AR1_CustomerMaster.SalesYTD}-{AR1_CustomerMaster.SalesPYR}) % {AR1_CustomerMaster.SalesYTD}

 

[lbass]

Peter,

If you used my original formula:

if {table.lastyearamt} = 0 then 0 else
({table.thisyearamt}-{table.lastyearamt}) % {table.lastyearamt}

...then if {table.thisyearamt} = 0 the result would be 100%, so I'm not sure what your concern is--unless you are saying that {table.thisyearamt} might be null?

-LB

 

[PeterL]

Exactly, {table.thisyearamt} might be null. It should show -100%.

 

[lbass]

Try:

if (
isnull({table.lastyearamt}) or
{table.lastyearamt} = 0
) then 0 else
if isnull({table.thisyearamt}) then -100 else
({table.thisyearamt}-{table.lastyearamt}) % {table.lastyearamt}

-LB

 

[lbass]

Sorry, I think it should be:

if (
isnull({table.lastyearamt}) or
{table.lastyearamt} = 0
) then 0 else
if isnull({table.thisyearamt}) then -1 else
({table.thisyearamt}-{table.lastyearamt}) % {table.lastyearamt}

-LB

 

[PeterL]

Almost, this is what the results look like

YTD PYR +-%
0 0 0 Good
10 0 0 Should be %100
0 10 -100 Good
47 177 -73 Good

Everything works fine but when the YTD has a value but the PYR doesnt.

 

[lbass]

I thought you wanted the percentage based on last year in the denominator (which I think is the more usual comparison). Please post the formula you are using.

-LB

 

[PeterL]

Basically I am looking to post the % change between the Current year and Prior year keeping in mind that either can be zero. Here is the formula:

if (
isnull({AR1_CustomerMaster.SalesPYR}) or
{AR1_CustomerMaster.SalesPYR} = 0
) then 0 else
if isnull({AR1_CustomerMaster.SalesYTD}) then -1 else
({AR1_CustomerMaster.SalesYTD}-{AR1_CustomerMaster.SalesPYR}) % {AR1_CustomerMaster.SalesPYR}


Thanks for all your effort. I appreciate the help.

 

[kskid]

YTD PYR +-%
0 0 0 Good
10 0 0 Should be %100
0 10 -100 Good
47 177 -73 Good

if {AR1_CustomerMaster.SalesYTD} = 0 and {AR1_CustomerMaster.SalesPYR} = 0 then
0
else if {AR1_CustomerMaster.SalesYTD} = 0 and {AR1_CustomerMaster.SalesPYR} > 0 then
-100
else if {AR1_CustomerMaster.SalesYTD} > 0 and {AR1_CustomerMaster.SalesPYR} = 0 then
100
else
({AR1_CustomerMaster.SalesYTD}-{AR1_CustomerMaster.SalesPYR}) % {AR1_CustomerMaster.SalesYTD}

 

[PeterL]

This code still returns a divide by zero, oups.

 

[lbass]

Please note that for:

YTD PYR %
10 0 0

...0% is the correct figure. Anything divided by 0 is 0. However, you could force the display of 100% by using:

if (
(
isnull({AR1_CustomerMaster.SalesPYR}) or
{AR1_CustomerMaster.SalesPYR} = 0
) and
(
isnull({AR1_CustomerMaster.SalesYTD}) or
{AR1_CustomerMaster.SalesYTD} = 0
)
) then 0 else
if (
isnull({AR1_CustomerMaster.SalesPYR}) or
{AR1_CustomerMaster.SalesPYR} = 0
) then 1 else
if isnull({AR1_CustomerMaster.SalesYTD}) then -1 else
({AR1_CustomerMaster.SalesYTD}-{AR1_CustomerMaster.SalesPYR}) % {AR1_CustomerMaster.SalesPYR}

-LB

 

[PeterL]

Using this formula everything looks good except I get:

YTD PYR +-%
150 0 1.00 This should read 100
0 150 -100 Perfect

 

[lbass]

I guess the 1's in the formula should be 100-> 100 and -100

-LB

 

[PeterL]

Who's better then you folks.

Thanks