Calculating Average Per Claim with Voids

[crogers111]

CR XI
SQL

I'm trying to determine the best/correct method to calculating an average given the following...

Here's a sample data set. Voided Claims can happen days/weeks later and get their own record in the database.

Claim# Charge Paid Paid/Void Date Paid/Void
111 600 400 6/05/2011 P
222 300 300 6/21/2011 P
444 200 100 6/30/2011 P
444 -200 -100 7/09/2011 V
555 500 400 7/12/2011 P
666 200 100 7/19/2011 P

My report is grouped and subtotoaled by claim#. My dilemma is when the report I'm creating is run for a Paid/Void date ranges of June 2011 and July 2011 separately the 2 data sets are:

Claim# Charge Paid Paid/Void Date Paid/Void
111 600 400 6/5/2011 P
222 300 300 6/21/2011 P
444 200 100 6/30/2011 P

Claim# Charge Paid Paid/Void Date Paid/Void

444 -200 -100 7/9/2011 V
555 500 400 7/12/2011 P
666 200 100 7/19/2011 P


In other words, it's conceivable that a Void can appear in a data set without it's original Claim.

What should be the formula to calculate Avg Paid per Claim for the 2nd data set ? i.e. Do I use a claim count of 1,2 or 3 ?
Do I include the -100 in the paid total ?

Keep in mind if I use the June 2011 paid data set all claims and totals would be used for the Avg per Claim as no voids are included.

If I use the orig dataset with all claims then I can exclude the orig and voided claim from the calculations completely (which is what I do) but it's the July 2011 data set that I'm unsure how to handle when a void exists without its original claim.

 

[Madawc]

Do all Voids come immediately after the Claim? If so, you should be able to do a Running Total that has the following test:

{Paid/Void} = "P"
or ({Paid/Void} = "V" and
    {Claim} = {revious({Claim})

If you use the automated Running Totals on {Charge}, you should be given an average as an option.

I could be wrong on this: it might be that you can't do a running total if the test is Previous. If so, you'd need to do the same thing with a Variable to accumulate the charges and another to count the relevant transactions.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP