Good. Pro version. :cool:
So this is what I'm thinking...
First, define a PaidClaims filter limiting results to Paid > 0. Now build a summary. Set the default filter to your new PaidClaims filter, and use the Claim as your key field, and the sum of the Paid amount as your measure field. This is your list of unique claim number which have been paid.
Export the summary to an Excel file; we'll use this to determine which claims for which no services have been paid.
Now back in the table window build an external lookup to your freshly exported summary, bringing in the claim number as PaidClaim.
Now build a new calculated field, ClaimStatus with this formula:
If the IsNull function isn't available to you in v7, use this instead:
1/0 gives you the same result: a null value.
Now create an Unpaid filter using the ClaimStatus field, and again create a new Unpaid Claims summary listing the Unpaid filter as the default, Claim number as the key field, and the Paid amount as the measure. It doesn't really matter what the measure field is.
That should give you a unique list of unpaid claim numbers.
How does that work for you?
Sorry I'm just now getting back. Been out of work sick. When I summarize my data it doesn't add up. For instance, there are 17,186 Unique Claim Numbers but when I followed the instructions this is what I got.
13,280 Paid Claims
4,105 Denied Claims
Is it possible some of the paid claims are still falling into the denied category because of the one service that is denied?
Am I right to assume that if a claim show any paid services amount anywhere it is paid but a zero total means it is not paid and THERE ARE NO OTHER CRITERIA or possible data oddities?
If so, extrapolating in my mind what an entire report might look like, I would be tempted to suggest you just grab each line as detail and the sum the 'paid' field in a summary with the key field of CLAIM NUMBER.
Anything that is zero is unpaid and therefore, I assume, a denied claim. Anything that is paid would be a paid claim.
Iirc you can filter on the measures in V7 in order to get a list of paid and unpaid. If the numbers still don't add up it would suggest
there may be something odd in the data or the way the template/model is reading it. Might you be picking up lines that are not real detail records - continuation lines after page breaks where records cross over a page for example - or something like that?
In theory in the summary each Claim number should only appear once. So a summary with the claim number as key and a count measure should make the duplicates visible since the count would be greater than 1.
A few thoughts to play around with.