Counting <> 0

  • Thread starter Thread starter Michael Noblet
  • Start date Start date
M

Michael Noblet

I have a report that is grouped by department and has the
following fileds:

department
Discharges (Number)
MedicarePayments (Number)

in the detail section for any given discharge there could
be any number of Medicare payments. for example

department Discharges MedicarePayments
Cardiology 1 2

For each departments detail section there are multiple
entries. I have a sum for the number of discharges in the
group footer. I need to take an average of the medicare
payments for the group. The problem arises in that I do
Not want the records with 0 for a Medicare payment to
count in the denominator of the average equation. so the
example below applies:

discharges MedicarePaymets
1 1
1 2
1 0
1 0

Mathematically speaking it the average should be .75 but
the number I need is 1.5 or 3/2. is there a way to count
the records for a group that do not equal 0 so they can
then be used in a math formula?
 
This works great but I am looking it over and I might just
be tired but could you walk me through the derivation so I
understand better what I am looking at?
 
The expression "MedicarePayments>0" will return either -1 for true or 0 for
false. Using Abs() converts this expression to either 1 or 0 which then gets
Sum()'d. This sum is actually a count of the number of records that match
the condition/expression.

The IIf() checks to see if the Sum() returns 0 since this would cause an
error if used as the denominator.

The average is the Sum of the payments divided by the count of payments
where the amount is greater than 0:

=IIf(Sum(Abs(MedicarePayments>0)) = 0, 0,
Sum(MedicarePayments)/Sum(Abs(MedicarePayments >0)))
 
If you are told there are too many closing parens then you don't have a matching
number of left and right parens OR the parens are out of order. The only way
for anyone here to troubleshoot that is for you to POST the expression that is
having the problem.
 
You can prepare your query with following criteria:

Discharge: [Table1]![MedPayments]<>0 (this will only count
medPays with none-zero amounts. In the same query you can
group the data. For 'Discharge' column - put 'COUNT' and
under 'Medicare Payment' - put 'SUM'.
 
Back
Top