Embedded In then, else not working 1

[Sweetworks]

I am helping my sales manager with a report. He wants to compare a prior Periods Sales vs. a Current Period Sales, and categorize them as Declined, New, or Renewed. He wants to break up each sales person sales by customer, then by one of these fields
I already have them grouped correctly; I’m just having a bit of trouble with this formula

My fields are PP (Prior Period Invoiced Sales), CP (Current Period Invoiced Sales)

Declined would be
PP >= 0 and CP = 0
New would be
PP = 0 and CP > 0
Renewed would be
PP > 0 and CP > 0


Sample Data

Customer PP CP Gain/ (Loss)
Acme 0 100 100
BAC 100 500 400
Doors 500 0 (500)
Grandma 0 0 0

My results should be

Acme New
BAC Renewed
Doors Decline
Grandma Decline

I am Getting

Acme New
BAC New
Doors Decline
Grandma Decline

This is the Formulas I am using
IF ({@CP Invoiced} = 0 and {@PP Invoiced} >= 0) then 'DECLINE' else
IF ({@CP Invoiced} > 0 and {@PP Invoiced} = 0) then 'NEW' else 'RENEW'

I’ve tried a couple other variations. Any Suggestions?

 

[vidru]

Can you post what's in your {@CP Invoiced} and {@PP Invoiced} formulas?

Crystal version and database/connectivity would be helpful as well.

-dave

 

[Sweetworks]

It's in Crystal 8.5 connecting to an Oracle 8.1.7.4.1

Here is {@PP Invoiced}, for {@PP Invoiced} just replace the PP Start and End Dates with CP.
_____________________
IF ({RECEIVABLE.INVOICE_DATE} in {?PP Start Date} to {?PP End Date}) THEN {RECEIVABLE_LINE.AMOUNT}else 0
_________________________

The {@CP Invoiced} and {@PP Invoiced} formulas are what is shown as PP and CP for my sample results
So for Customer BAC {@CP Invoiced}= 500, and {@PP Invoiced} = 100. My Formula
_________________________________________________________
IF ({@CP Invoiced} = 0 and {@PP Invoiced} >= 0) then 'DECLINE' else
IF ({@CP Invoiced} > 0 and {@PP Invoiced} = 0) then 'NEW' else 'RENEW'
_________________________________________________________
should be resulting in Renew but it comes up as new.
As CP is greater than Zero, it ignores the first true statement and goes to the False Statement which further quires and ends up false again which should result in 'Renew'

 

[mbarron]

Are these results on your report canvas?
{@CP Invoiced}= 500, and {@PP Invoiced} = 100

The reason I ask is because your logic and stucture are obviously correct. Is it possible that the date is not in your interval. If you are certain that the dates are in the range try this:

whileprintingrecords;
IF ({@CP Invoiced} = 0 and {@PP Invoiced} >= 0) then 'DECLINE' else
IF ({@CP Invoiced} > 0 and {@PP Invoiced} = 0) then 'NEW' else 'RENEW'


Mike

 

[Sweetworks]

OK... I solved it.

On thing I neclected to inform you all of is that we were looking at the SUM of {@CP Invoiced} and {@PP Invoiced}, summed under Group 3.

I changed the Formula as follows and it is now working.

whileprintingrecords;
IF (Sum ({@CP Invoiced}, {CUSTOMER.ID}) = 0 and Sum ({@PP Invoiced}, {CUSTOMER.ID}) = 0) then 'DECLINE' else
IF (Sum ({@CP Invoiced}, {CUSTOMER.ID}) = 0 and Sum ({@PP Invoiced}, {CUSTOMER.ID}) > 0) then 'DECLINE' else
IF (Sum ({@CP Invoiced}, {CUSTOMER.ID}) > 0 and Sum ({@PP Invoiced}, {CUSTOMER.ID}) = 0) then 'NEW' else 'RENEW'