5 Replies Latest reply: May 15, 2014 9:55 AM by Gareth Horton RSS

    summing a value from an appended template

    Steve_P _

      I have just put together a model to export our trial balance from a text document to excel.  The report includes a single line of info for a patient (account number, name, account balance), plus additional lines for their insurance information (ins name, ins balance). 

       

      i.e.

       

      11111111  smith, john  1000.00  ins1 250.00

                                      ins2 500.00

                                      ins3 250.00

       

      I set up the insurance information as my detail, and the patient information as my append, so when I go to table view, I have

       

      11111111  smith, john  1000.00  ins1 250.00

      11111111  smith, john  1000.00  ins2 500.00

      11111111  smith, john  1000.00  ins3 250.00

       

      Maybe I am overlooking a basic function, but when I try to do a summary and sum up my account balance, I get 3000.00 for smith, john. What do I need to do to properly sum the account balances?

       

      Thanks for your help - I hope you understand all of my babbling!

        • summing a value from an appended template
          DY*BIM _

          In the properties of the measure, try and use Maximum or Minimum for the account balance amount.

           

          dy

          • summing a value from an appended template
            Steve_P _

            Thanks for the reply!  I have just tried the above mentioned solution - this works to show the correct account balance on each line of the summary, but then at the bottom of the summary, I lose my 'Summary' which was supposed to be by total of all account balances (it looks like this is now just giving me the highest/lowest account balance from the above list).

            Any other solutions?

            • summing a value from an appended template
              DY*BIM _

              I tried to create a calculated field with formula that will assign 0 to the value if the account number is the same as the previous record account number (think Excel:  IF(A3=A2,0,B3) where A is AccountNumber and B is AccountBalance).

               

              I was unable to find a function that will accomodate it in Monarch.  I am able to identify rowno() and recno(), but how do you leverage it to compere values?

               

              If anyone know a way of doing it, it will be grand, and it will solve the summing issue.

               

              dy

              • summing a value from an appended template
                Grant Perkins

                Steve,

                 

                An interesting report which may allow us to be creative.

                 

                Have you tried changing the summary MEASURE field properties to use the AVG() function?  AVG(Balance) should work in this instance. (per the information provided of course!)

                 

                Or UNIQUE might do it for similar reasons.

                 

                 

                Grant

                 

                  Originally posted by Steve_P:

                Thanks for the reply!  I have just tried the above mentioned solution - this works to show the correct account balance on each line of the summary, but then at the bottom of the summary, I lose my 'Summary' which was supposed to be by total of all account balances (it looks like this is now just giving me the highest/lowest account balance from the above list).

                Any other solutions? /b[/quote]

                 

                [size="1"][ July 07, 2004, 08:19 PM: Message edited by: Grant Perkins ][/size]

                • summing a value from an appended template
                  Gareth Horton

                  Steve

                   

                  The problem here is that you are counting the balances as many times as there are insurance information.

                   

                  If you need only the "total" balances summed, then just take the the patient info and balance line as a detail and do a summary on the balance.

                   

                  That will give you what you want.

                   

                   

                  Originally posted by Steve_P:

                  I have just put together a model to export our trial balance from a text document to excel.  The report includes a single line of info for a patient (account number, name, account balance), plus additional lines for their insurance information (ins name, ins balance). 

                   

                  i.e.

                   

                  11111111  smith, john  1000.00  ins1 250.00

                                                  ins2 500.00

                                                  ins3 250.00

                   

                  I set up the insurance information as my detail, and the patient information as my append, so when I go to table view, I have

                   

                  11111111  smith, john  1000.00  ins1 250.00

                  11111111  smith, john  1000.00  ins2 500.00

                  11111111  smith, john  1000.00  ins3 250.00

                   

                  Maybe I am overlooking a basic function, but when I try to do a summary and sum up my account balance, I get 3000.00 for smith, john. What do I need to do to properly sum the account balances?

                   

                  Thanks for your help - I hope you understand all of my babbling! [/b][/quote]