CR11 running total divided by distinct count 1

[azoe]

I have a formula in the report footer that divides a running total by a distinct count. The result is 26.9 but if I do the division manually the result is 23.918.

Is there any issue with dividing these two types of formulas or could it be some kind of timing issue?

Thanks -

 

[DataDog]

I have found it helpful to put a "Whileprintingrecords;" before doing any calculations.

DataDog
'Failure Is Not An Option'

 

[azoe]

I added that:
WhilePrintingRecords;
{#GrandTotalDateDiff}/{@GrandTotalNumberOfCharges}

And got the same result as before.

But thank you.

 

[lbass]

Did you display the running total and the formula in the report footer to verify that you are using the correct figures in the manual calculation?

-LB

 

[azoe]

Yes, because I wanted to verify those figures from the detail - or at least the group level.

I think there is a problem because the running total is affected by my groups and the distinct count must be looking at all the id's in one set instead of counting unique per group.

Back to the drawing board. I don't think anyone else can help me at this point.
Thanks anyway -

 

[lbass]

If you provide more explanation about the report structure, the contents of your formulas, and how the running total was developed, along with some sample data, I think someone could help.

-LB

 

[azoe]

Thanks - I will try to explain.

The point of the report is to find out how much time (in days) pass between when we apply the charge to the encounter and when we get the payment from each insurance company.

There are 4 groups. The first one is the financial class of the payer, then whether the payer is primary, secondary or tertiary, then the payer name, and last the encounter (this is what the patient's charges are attached to).

Payments are associated with charges and so are adjustments. So part of my challenge is that if there is a $100 charge and a $80 payment and a $20 adjustment the $100 appears in the results two times but I only want to count it once per payer.

The running total field to summarize is DateDiff which finds the number of days between the charge and payment post dates. I evaluate it on change of the charge_id (key field in charges table) and reset never. Since one of the groups in the report is primary and secondary payer an encounter may show up in both of those groups. There may be a payment from the primary payer and one from the secondary payer. So I believe it is counting the DateDiff for each of those (which is ok because it is two different payers).

The formula that counts the number of charges is WhilePrintingRecords;
DistinctCount ({aa_dch_LagTime;1.charge_id}). I'm not a formula expert but I'm thinking it is looking at all the charge_ids in one group and only counting a particular charge_id one time (where in the running total it may be counting it twice because of the groups, right? once in primary and once in secondary). Because I ran the report for a short period of time and counted them and the results of the formula only counted that charge_id one time.

The last formula just divides those two to get the average number of "lag time" days. But if it is doing what I described then it is inaccurate.

I did these averages in the group footers also. So most of the time they will look at the report with the details suppressed and it will show:
Financial Class of Payer
Primary Average:
Secondary Average:
Average for the financial class:

That repeats for all 23 financial classes and then I have the formulas described above in the report footer.

Sample data - not sure how much detail helps but if you don't suppress the details or any of the groups you get:

Financial Class of Payer (ex. CO)
"Primary Payer"(this is a 1 in the database)
Payer Name (Aetna)
Encounter (a number like 12345)
Details: Encounter DateDiff ChgAmt PayAmt AdjAmt
12345 18 100.00 40.00 Null
12345 18 100.00 Null 20.00
22355 10 50.00 50.00 Null
"Secondary Payer"
12345 25 100.00 40.00 Null


Does that make sense?
Thank you -

 

[lbass]

A couple of comments. For your formulas at the group levels, you should have separate running totals for each group level, where the reset is based on the group level where you want the result. I think you should also use a running total for the count field, instead of a distinct count. Insert a running total where you select {aa_dch_LagTime;1.charge_id}, distinctcount, evaluate on change of group (encounter), reset on change of group(choose the appropriate group). Then create a formula:

{#datediff}/{#countchgID}

You will need two running totals and a formula like this for each group level or at the report level.

Note that you didn't show the datefields, and I'm assuming you want the datediff on the field where this is a payment, not where there is a null, so you have to be sure you are sorting the payment field so that the nulls are last.

-LB

 

[azoe]

I have to leave in a minute but while this is still fresh -
I think that is a good idea to have the running total for the count - then they should be treated the same.

Although not shown I did have the datediff on every line because I thought they wanted it for the adjustments too but I will double-check that.

I discovered I had to sort by charge_id because if I didn't the running total didn't work (since there may be two and I think it just compares one charge_id with the one on the next line, so if they are not together, "on change of" field doesn't work right.

Thanks so much for your comments and I will try them out Monday.

 

[azoe]

Oh, one more thing.
In the report footer they would like an average for all the primary (not broken up by financial class etc.) and an average for the secondary, and then one overall.

I've been trying to do this with formulas but have not yet been successful. Do you think that is something that can be done with a formula or might I have to do a separate query and a subreport?

Thank you -

 

[lbass]

You can use running totals for this also, where you use the evaluate using a formula option. Then you would limit the records to primary or secondary in this formula area by using a formula like:

{table.payer} = "Primary" and
{table.encounter} <> previous({table.encounter})

I'm unclear about whether chg_ID is the same as encounter. So whatever you are evaluating "on change of" belongs in the second part of the formula.

-LB

 

[azoe]

Thank you SO much - I had never used "previous" and didn't even know about it.
That worked!