Insert Sum into Formula?

[genekr]

Crystal 9, PSQL 9, W2003 server.
I have a report where I'm taking a specific Code Category, Sale amount, dividing it by Lbs sold to get $/Lb.

Code Total Lbs Sold Sale Amount $/Lb
60 172.80 $392.80 $2.2731

For Total Lbs Sold I'm using {Qty Sold} * {Item Weight}
For Sale amount I'm taking the (Sum(Sale Amount)) and for
$/Lb I'm taking (Sum(Sale Amount)) and dividing by Total Lbs Sold.

The actual computed (w/calculator) for Code 60 is $2.2731, but when I run my report I get a figure of $2.0371.

For Sale Amount there is more than 1 amount that it's adding up for total, but when it tries to figure out the $/Lb, it's only taking the first amount in the total field, hence giving an incorrect $/Lb total.

Hope that's clear enough. Any help is appreciated.

PS-I tried inserting the (Sum(Sale Amount)) into my $/Lb formula but get error:
A summary has been specified on a non-recurring field.

Gene

 

[genekr]

Actually for Sale Amount, I'm taking (Sale Amount)/Total Qty Sold. I couldn't enter a Sum for a formula. All these calculations I'm putting in the Group header because I'm grouping on the Code.

 

[synapsevampire]

You should take the time to post the actual formulas because your post doesn't make sense.

If you used Sale Amount)/Total Qty Sold in a group header, you'd only get the 1st row of that group used in that formula.

If you're grouping on the code, then you should be able to use sum formulas of:

if Count({table.Sale Amount},{table.code}) > 0 then
sum({table.Sale Amount},{table.code})/Count({table.Sale Amount},{table.code})
else
0

You can use aggregates in a formula the group header is the point, but if you want to display each sales information that will need to be placed in the details.

-k

 

[genekr]

Thank you. I tried using this as a formula in the Group Header

if Count({cphsttrx.hist_trx_sale_amt},{imitmfil.item_prod_cat}) > 0 then
sum({cphsttrx.hist_trx_sale_amt},{imitmfil.item_prod_cat})
/Count({cphsttrx.hist_trx_sale_amt},{imitmfil.item_prod_cat})
else
0

but I still get the error

A summary has been specified on a non-recurring field.

 

[synapsevampire]

And you're grouped by the imitmfil.item_prod_cat?

Very strange, perhaps it's null related.

Try the following formula for the group field:

if isnull({imitmfil.item_prod_cat}) then
0
else
{imitmfil.item_prod_cat}

Then reference this formula within the aggregate formulas.

-k

 

[genekr]

I put:
if isnull({imitmfil.item_prod_cat}) then
0
else
{imitmfil.item_prod_cat}

in a formula but it's giving me an error that says:

"A number is required here" and it highlights the last line of the formula

{imitmfil.item_prod_cat}

The field item_prod_cat is a char field with a size of 3 in PSQL. There are alpha and numerics in the field. Didn't know if that was relevent or not.
Thanks again.

 

[genekr]

Yes, grouped on {imitmfil.item_prod_cat}

 

[synapsevampire]

Sorry, didn't realize your level of expertise, try:

if isnull({imitmfil.item_prod_cat}) then
"N/A"
else
{imitmfil.item_prod_cat}