CR11 Want to calculate Average not counting records with zero 2

[azoe]

I have a report that lists customer charges and payments.
It is grouped by a financial code and then whether it is a new or existing customer.

In the second group I'd like to show an average charge per customer. The problem is that sometimes there is a payment and no charge yet. So I only want to divide the total charge figure in each group by the number of customers that actually had charges. I've typed in the average as I'd like it to calculate below.

Likewise I'd like to show average payments per customer but sometimes there are charges and no payments. I'd like to be able to divide the payment figure by only the number of people that actually had payments.

Data looks like this:
Charges Payments
Financial code FCS
New Customers
Customer 1 50.00 0.00
Customer 2 0.00 30.00
Customer 2 0.00 20.00
Customer 3 0.00 10.00
New Cust grp footer Avg Chg: 50.00 Avg Pmt: 30.00
Existing Customers
Customer 4 100.00 0.00
Customer 5 200.00 50.00
ExistCust grp footer Avg Chg: 150.00 AvgPmt:50.00

I tried the Average function but it shows the same number for both the New and Existing.
For the group totals of charges and payments I just used the insert summary.
Thanks

 

[lbass]

If you use the average function at a group level, you have to add the group condition, as in:

average({table.payments},{table.neworexisting})

Anyway, to exclude the zero payments, you could insert a running total that averages {table.payments}, evaluate using a formula:

{table.payment} <> 0

Reset on change of group (neworexisting).

Place the running total in the group footer for neworexisting.

-LB

 

[hilfy]

You'll actually need a couple of formulas to do this.

First, you'll need to indicate whether a customer has a charge:

{@HasCharge}
If {table.charge} > 0 then 1 else 0

Then, you'll use this to get the charge average per customer type (new vs. existing):

{@AvgCharge per type}
If (sum({@HasCharge}, {table.customer_type}) > 0 then
sum({table.charge}, {table.customer_type})/sum({@HasCharge), {table.customer_type})
else 0

(note the check to avoid "divide by zero" errors!)

You would do the same sort of thing for average payments.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.