CR 8.5 Problem with Percentage in Crosstab

[TaylorTot]

I am currently using Crystal V.8.5 I have built a cross tab with the following fields:

1) Contracts.NetAmount

2) If not ({Industries.Code} in "ACB", "ACC", "ACA", "TRA", "TRB", "TRN"]) then {Contracts.TradeDiscount} else 0 (TradeDiscount)

3) If ({@trade discount})
> 0 then ({@trade discount})/({Contracts.NetAmount})*100 else 0 (TradePercentage)


I have summed field 1 and field 2, then used a weighted average of field 3 with field 1.

The percentages are right about 60% of the time but they are not always correct. Any ideas would be greatly appreciated!

 

[smcnulty2000]

I'm not sure why you are checking for ({@trade discount})>0

It seems with this equation that you would want to check for ({Contracts.NetAmount}) <>0 so that you don't get a divide by zero error. If some of your trade discounts aren't positive, this would throw a wrench in things.

I can't guarantee that the cross-tabs will always crash on a divide by zero error, either, since I've seen charts survive them.

I'd put parenthesis around this from formula 3:

({@trade discount}/{Contracts.NetAmount}))

just for readability, and because I happen to be that way.

Maybe you could post some of your wrong data? It's funny how data that's wrong all of the time is usually easier to debug than data that's wrong only some of the time.

You are also missing a left square bracket in formula 2, but no doubt you would have gotten an error message if your original formula had that missing.

scott.

 

[lbass]

What are your row and column fields? Are you using more than one table? It sounds like you might have row inflation.

-LB

 

[TaylorTot]

Here is what the table looks like

Year
Region Code Total Sales
Total Trade Discount
% of Trade Discount

Data:

2004 2005 Total
Anaheim $976,301.94 $779,889.95 $1,756,191.89
$14,286.25 $4,582.25 $18,868.5
1.16% 0.59% 0.90%

As you can see the 2004 percentage is incorrect but the 2005 is correct.

Thanks for any help!!

 

[smcnulty2000]

This is most peculiar. I note that the .90% is wrong as well.

I also note that the number that is most correct is the one that is the lowest in the number pairs. Further, the true percentage is a higher number in the two incorrect cases. Not lower, not one each. That may or may not be significant but I'm inclined at this instant to believe it is significant.

Put the phrase " whileprintingrecords; " at the beginning of each formula and tell us if anything changes. Or tell us if it is already there. Start with formula three and run the report. Include the semicolon but not the quotes.

If this is an evaluation time issue, that might tell us. This is almost like stuff I've seen where a formula gets calculated before all the data gets in.

scott.