Crystal Reports: this should be simple but apparently it isn't

Factual Questions
1

I am trying to construct a simple formula for percent of variance, comparing revenue this year vs. last year.

A is this year’s revenue (a sum of revenue from work orders for each account).
B is last year’s revenue (as above).

The basic formula would be (A-B)/B

However, you need to account for cases where B = 0, or you will get Division By 0 errors.

I want to say that if this year is greater than $0 and last year = $0, then variance is +100%, or if this year is less than or = $0 and last year = $0 then variance is 0%, for all other cases use the basic formula. So this is my formula syntax:

if A > 0 and B = 0 then 1 else
if A <= 0 and B = 0 then 0 else
(A-B)/B

This does not work, especially in the first case; I don’t get 100% for any combinations. I only get 0% for all cases where B=0, or else I get the correct calculation when B <> 0.

What am I doing wrong?
Roddy

2

Var = 0;

if A > 0 and B = 0 then Var = 1;

If B > 0 then Var = (A-B)/B;

you don’t need to nest the if’s. the 2nd if will Never be true when the first if is true.

set var = 0 as a default. it kicks in when neither if is true

3

last statement

Var;

that tells crystal to return the value of the variable Var

4

crap, forgot the : Assignment needs :=

var := 0;

but you knew that I bet.

5

Heh, no, I’m pretty simple-minded and self-taught where formula construction is concerned.

Anyway, thanks and I will try that.
Roddy

6

also you need to declare the variable Var. I do these in the first lines of the formula.

Local NumberVar Var;

Strings are declared
Local StringVar EmpName;

I’m guessing your A and B are in your database? So those don’t require declaring.

7

Thanks for your help, folks, but I am still having a problem; now I am getting “True” or “False” for the result instead of a numeric value.

Here is the actual formula:

Local NumberVar Var;
Var := 0;
if Sum ({@CY YTD}, {@CATEGORIES}) > 0 and Sum ({@PY YTD}, {@CATEGORIES}) = 0 then Var = 100;
if Sum ({@PY YTD}, {@CATEGORIES}) <> 0 then Var = (Sum ({@CY YTD}, {@CATEGORIES}) - Sum ({@PY YTD}, {@CATEGORIES}))/Sum ({@PY YTD}, {@CATEGORIES})*100

The two fields I am trying to compare are subtotals for the current year (CY) and prior year (PY).

Can someone wiser than I am please take pity and tell me why I am getting values of “True” or “False” for this formula? What I want to get is the value of
(Sum ({@CY YTD}, {@CATEGORIES}) - Sum ({@PY YTD}, {@CATEGORIES}))/Sum ({@PY YTD}, {@CATEGORIES})*100

Stupid lack of programming training!
Roddy

8

I just read through the thread again and discovered that I had left off the Var; at the end. Now it works.

::slinking off in embarrassment::
Roddy