Issues with my dataset mapping or my sumover logic - need guidance

Q&A
,
1

I have a dataset as described below:

Claim (1 to N) Procedures ( 1 to 1 ) Charges
(1 to N) Payments
(1 to N) Adjustments

To get the charges of claim, I do a sumOver(charges, [{claim_id}], PRE_AGG). The charges are getting multiplied by the number of payments and adjustments for that claim (table below).

image
image1950×416 62.7 KB

Charges for this claim should be: $130.00. My sumOver is generating $1040.00.

Payments should be: $37.25. My sumOver is generating $74.5.

What am I doing wrong? Is it the mapping on my dataset? How do I remove this duplications?

TIA

2

Hi @andreduarte

You are not doing anything wrong in the expression itself your dataset grain is the issue.

All rows in your visual are at the claim * procedure * payment/adjustment level. So, each charge row is duplicated once for every payment/adjustment row. sumOver(charges, [{claim_id}], PRE_AGG) just sums all those duplicated values, so you get 130 * 8 = 1040.

If charges is constant per claim (or per claim+procedure), use a min/max at the claim level instead of sumOver.

Example:

ClaimCharges = maxOver({totalcharges}, [{claim_id}], PRE_AGG)

ClaimPayments = sumOver({paymentamount}, [{claim_id}], PRE_AGG) / distinct_countOver({payment_id}, [{claim_id}], PRE_AGG)

3

Interesting. I’ve used min max and got the right results but I was thinking this was more like a workaround than a solution. Thanks for your feedback!

4

@Xclipse this is working on a claim level. How do I use the ClaimCharges to generate a total sum for all claims on my dataset? Using sumOver or simply adding the ClaimCharges filter will generate the same issue as before.