Divide by 0 in a report alert 1

[DaveyDSmith]

I know how to correct a div by 0 error by using If {Table.Field}=0 then 0 else... But I have a div by 0 error in a Report Alert. It does not seem to like If statements. Using CR v 2008.

 

[abhi900]

I am glad someone posted this again,.I had and still have similar issue and jst cant get a way out of it.

If conditions does not seem to be appropiate syntax for such error handling techniques.

regards

 

[lbass]

Please show the content of the formula you used.

-LB

 

[DaveyDSmith]

My report compares the # & $ Sales between a user-defined Period 1 & Period 2. The alert is triggered for any customer whose Sales have decreased by a ?Parameter amount. The condition statement I used was >> (((Sum ({@Row Sale 2}, {Customers.Customer_Type})-Sum ({@Row Sale 1}, {Customers.Customer_Type}))/Sum ({@Row Sale 2}, {Customers.Customer_Type}))*100)>={?%Decrease} Once I got the divide by 0 error when I tried to view the records that triggered the alert, I added my old familiar code to it >> if Sum ({@Row Sale 2}, {Customers.Customer_Type})=0 or Sum ({@Row Sale 1}, {Customers.Customer_Type})=0 then 0 else (((Sum ({@Row Sale 2}, {Customers.Customer_Type})-Sum ({@Row Sale 1}, {Customers.Customer_Type}))/Sum ({@Row Sale 2}, {Customers.Customer_Type}))*100)>={?%Decrease} & got an error "A number is required here" Well, the result of the condition statement is actually a True/False (in order to trigger the alert) and truly is NOT a number. I know of no other way to stop Div x 0.

 

[lbass]

What happens if you enter this as a new formula and place it on the report?

I also find it unusual that you are using what is a report level alert to try to identify group level issues. I'm not sure alerts work that way.

-LB

 

[lbass]

Okay I just tested this and you can test for group level results. I wonder whether your parameter is a datatype that matches the datatype of your summary.

-LB

 

[DaveyDSmith]

I have used alerts for groups in the past & it is pretty slick. I think I have determined why I am having trouble with this alert. In the past my condition statements did not have a need for division. Sales Period 1 < Sales Period 2. The nature of alerts, the way I understand them is they must return a true/false result. That is what the trigger is all about. My added code to eliminate the div by 0 issue will return a 0 if variable 1 or variable 2 = 0. Well, 0 is not a boolean and will not compute. Really wish there were another way, but I am not sure there is.

 

[lbass]

Try this:

(
if Sum ({@Row Sale 2}, {Customers.Customer_Type})=0 or
Sum ({@Row Sale 1}, {Customers.Customer_Type})=0 then
0 else
(
Sum ({@Row Sale 2}, {Customers.Customer_Type})-
Sum ({@Row Sale 1}, {Customers.Customer_Type})
)/Sum ({@Row Sale 2}, {Customers.Customer_Type})*100
)
>={?%Decrease}

-LB

 

[DaveyDSmith]

That is exactly the same formula as what I posted above. For a normal formula that works great and returns a 0 instead of a div by 0 error. But for an alert, it is looking for a true or false. Even when you try to save that formula in the alert condition screen, you get an error that says "the remaining text must be a number.

 

[lbass]

No, it's not the same formula.

-LB

 

[DaveyDSmith]

yes, I did not see the opening parens. That solves the trouble, because now all I am doing is comparing a # to a #. I did have to make a slight adjustment so I did not miss the records that were equal to 0. The user input might be 20 (%) and if the Period 1 = 0, that would be a decrease over Period 2, therefore the record should be part of the alert group. If I exchange the 0 for a 100, it picks it up properly. My final formula is below. Thanks for your help and hope this helps the other person who had similar trouble.
(if Sum ({@Row Sale 2}, {Customers.Customer_Type})=0 then 0 else if Sum ({@Row Sale 1}, {Customers.Customer_Type})=0 then 100 else
(Sum ({@Row Sale 2}, {Customers.Customer_Type})-Sum ({@Row Sale 1}, {Customers.Customer_Type}))/Sum ({@Row Sale 2}, {Customers.Customer_Type})*100)>={?%Decrease}

 

[abhi900]

Hi,
I had a similar issue with Divide by Zero error.. so i hope its ok to put my query in here as well than create a new thread.

Well,
i usually get an error on the formual field "DateDiff" which holds:-
{Query.combined} - (today-1)
So i guess when the part (today-1) is 0 its comes as divide by zero error.
I tired putting in IF statement but in vain.
and
the {Query.combined} contains following code :-
Select
td.TESTCYCL.TC_TEST_ID,
td.TESTCYCL.TC_EXEC_DATE As 'combined',
td.TESTCYCL.TC_STATUS,
td.TESTCYCL.TC_TESTCYCL_ID,
td.TESTCYCL.TC_CYCLE_ID
From
td.TESTCYCL
Union
Select
td.TESTCYCL.TC_TEST_ID,
td.TESTCYCL.TC_PLAN_SCHEDULING_DATE,
td.TESTCYCL.TC_STATUS,
td.TESTCYCL.TC_TESTCYCL_ID,
td.TESTCYCL.TC_CYCLE_ID
From
td.TESTCYCL
Order By
td.TESTCYCL.TC_TEST_ID

So please advise,

regards
abhi.

 

[IanWaterman]

You should start a new thread, otherwise people may not read, especially if thread looks closed when marked with a star.

Its the datediff() returning 0 which is the problem

Try

If datediff("d", {Query.combined},(today-1) = 0 then 0 else
..... whatver you current formula is.

Ian