2 Replies Latest reply: May 15, 2014 9:57 AM by RalphB _ RSS

    Newbie with unique problem

    CK77 _

      I have a unique problem I would greatly appreciate some assistance with. I do all types of insurance reports for my company but the most basic of them is a paid/denied report. This is needed when the states we have policies in want to see if we’re denying their claims for the right reasons and paying their claims on time. I have been using the covered expense field (amount charged minus the amount excluded) to determine if a claim is paid or denied. The problem is we can have multiple services or benefits on each claim. Some benefits can be covered while others are denied.  The only way I can determine if a claim is completely denied is to make sure all of the charges have been excluded.  In the example below you can see neither of the benefits had a payment but the $125 allowed amount applied to the deductible is the only thing that keeps this claim from being a denied claim.


      CLAIM     PAID     PAID AMT     AMT CGD     AMT EXCL     Remark Code     Allowed

      0702AF003I     01/31/2007     0.00     250.00     125.00     05     125.00

      0702AF003I     02/23/2007     0.00     37.50     37.50     28     0.00


      I guess I am looking for a way to condense the claim lines and still retain the important pieces such as the remark code, type of service, and place of service. Is there a way to do this or can anyone offer an alternate solution? Thanks for your time.

        • Newbie with unique problem

          My first thought would be to create a summary that totals your money fields based on the key (claim number?).  Export it to Excel.  Then, create a second model that uses the first Export as an external lookup based on the key.  You will then have detail records along with the summary total for each detail.  The money fields that you are importing as a lookup can be used to see if the claim was denied or not.  Let me know if you have any questions.

          • Newbie with unique problem
            RalphB _

            Hi CK and welcome to the forum.


            You don't say if you have the Pro version of Monarch or not, but if you do, Joey was on the right track.


            Since you already have a calculated field telling you if there is an outstanding amount or not I would first set up a summary with the claim number as the key field and your calculated field as the measure and export that out.


            Next, with a second model, I would link back to the summary  you exported using external lookups linking with the claim number field and bringing in that calc field.  Then run a filter showing only claims that have an amount in the new calc field.  This would show all claims that have an outstanding amount and all services and benefits whether paid or not.  This would show both lines you have in your example and would not show both if both were denied.