6 Replies Latest reply: May 15, 2014 10:09 AM by Grant Perkins RSS

    Too many Account Balances in Total

    stcharlesdr _

      I have a Monarch problem where I have made the detail record an 'error message' which can occur one or many times, in order to capture all the error messages.  Then I created an Append record to capture the patient detail which occurs above the first error message.

       

      The problem I've encountered is that if there is an 'account balance' in the append record (the line with the patient name in it), it will appear in the results multiple times (as many times as there are error messages) invalidating the account totals when summed later.

       

      Raw Data Example[/U]:

                   Patient Name                Account No  P/I   F/C   P/T    Admit    Discharge  Unbilled     DR      DR       Balance      

                     Pre/Reg by     Severity                    Error Message                        Ctrl By                    Loc CPTAFB   

      -


           

      TEST,PATIENT                           10642-00902z   E     SP   ERB   09/05/10   09/05/10         2    12288   12288      $86.30       /B

                        /PPP            F     ABSTRACT COMPLETE DATE is Required                     Health Info Management     PA    No     

                                        F     PRINCIPAL DIAGNOSIS CODE is Required                   Health Info Management                   /CODE

       

      Extract Example[/U]:

      report_name     Billed on Date     Patient Name     Account #     Pt Type     Admit Date     Dschg Date     Days Unbilled     Account Balance     Error Message     Ctrl By     Pre Reg By

      FBR210B     09/07/10     TEST, PATIENT           10642-00902z     ERB     09/05/10     09/05/10     2     86.3[/B]     ABSTRACT COMPLETE DATE is Required     Health Info Management     PPP

      FBR210B     09/07/10     TEST, PATIENT          10642-00902z     ERB     09/05/10     09/05/10     2     86.3[/B]     PRINCIPAL DIAGNOSIS CODE is Required     Health Info Management     PPP[/CODE]

       

      I've tried many permutations in the model to try and resolve this using 1 pass through with Monarch.  Any ideas would be appreciated!

        • Too many Account Balances in Total
          elginreigner _

          How are you totaling the patient values? I'm assuming via a summary. The summary can remove duplicate entries if you need.

            • Too many Account Balances in Total
              stcharlesdr _

              I was totalling in another program and just having Monarch return the raw data.  I'm experimenting with the Summary function now in Monarch.  So far, I'm not getting it to work.  I've tried the 'Unique' function, but that isn't getting it.  How would I remove duplicate values?

                • Too many Account Balances in Total
                  Data Kruncher

                  If you're getting multiple lines in the Table window for each patient, and the account balance is the same on each line, then build a summary using the account balance as a measure, and change the default Sum operation to Average (double-click the measure field, go to the Calculation tab, and select Average).

                   

                  As the amount is the same in each instance, the Average operation will return the proper single amount.

                    • Too many Account Balances in Total
                      Data Kruncher

                      That said, you could perhaps employ a "guru trap" to generate a single record for each patient, which would include all of the applicable error messages, and would alleviate the need to use the summary "trick".

                      • Too many Account Balances in Total
                        stcharlesdr _

                        If you're getting multiple lines in the Table window for each patient, and the account balance is the same on each line, then build a summary using the account balance as a measure, and change the default Sum operation to Average (double-click the measure field, go to the Calculation tab, and select Average).

                         

                        As the amount is the same in each instance, the Average operation will return the proper single amount.[/QUOTE]

                         

                        Thanks Data Kruncher, I tried that, but I still get the same account balance in each line with a unique error message.  In the below example, using the average function, 7.4 is returned on each line.  7.4 will get added to 7.4 for this patient in a program this data gets loaded into returning a result that is too high:

                         

                        Patient Name     Account #     Pt Type     Admit Date     Dschg Date     Error Message     AVG(Account Balance)     AVG(Days Unbilled)

                        TEST, PATIENT     XXXX9-00XXXp     ERB     09/06/10     09/07/10     ABSTRACT COMPLETE DATE is Required     7.4     0

                        TEST, PATIENT     XXXX9-00XXXp     ERB     09/06/10     09/07/10     PRINCIPAL DIAGNOSIS CODE is Required     7.4     0[/CODE]

                         

                        If you could make a specific recommendation as how to build the guru trap in this instance it would be appreciated.  I haven't built one yet...

                         

                        Thanks.

                          • Too many Account Balances in Total
                            Grant Perkins

                            If you just need the patient account and then a list of one or more error codes I don't think using the error code line(s) as detail templates is the way to go.

                             

                            On the other hand if you are dong the analysis primarily on the error codes but just want associated information about the state of the account then you may indeed need to set the error code lines as detail - but there may also be other ways.

                             

                            Do all the Account records in the report exist thee bacause they have at least one error code line - or can there be account lines without error codes? If there are accounts without errors does each account always consist of at least 2 lines on the report?

                             

                            If you always have at least 2 lines for an account, trap on the main account line using a 2 line data sample. Paint the fields you need from the first line and any fields you need from the second line that only appear once (e.g. maybe the "/ppp" data?).

                             

                            For field positions which may be repeated for multiple lines define the field on the one line available in the sample window but make the field a 'multi-line' field using the advanced field properties. One (or more) of the 'End field on' properties should get what you want. (Assuming that there is no compexity introduced by other aspects of the report which have not been sampled here yet!)

                             

                            You can then treat the error lines as a block of text or you can do something with them using the TEXTLINE function or other 'Slice and Dice' options - but lets get into that if and when we need to.

                             

                            HTH.

                             

                             

                            Grant

                             

                            ETA - This is NOT the 'guru trap' technique. We may not need that if this works ....