3 Replies Latest reply on Aug 22, 2017 8:01 AM by Jim Dehner

    Finding an outstanding balance based on ID #s

    Amy Carrier

      Good morning!  I am having difficulties with a calculation that I thought would be easy (ha). 

       

      In the attached workbook, you can see a number of transactions.  Some are new pledges (Contrib Type 'NP') and some are payments on those pledges (Contrib Type 'PP').  Each pledge has an ID #.  I am trying to create a calculation that will allow me to subtract the pledge payment amount (calculation 2) from the original pledge amount (calculation 1), based on whether the Pledge ID is the same as the Pledge Payment ID, AND if the Gl No (third column) is also the same.

       

      Any suggestions would be greatly appreciated!

       

      Thank you,

       

      amy

        • 1. Re: Finding an outstanding balance based on ID #s
          Jim Dehner

          Hi Amy

           

          Is this what you are looking for:

           

           

          Or in summary form

           

          The difficulty you are having results from creating the separate pledge and payment fields and the nulls that go with them -

          using the formula           sum(if [Contrib Type]='NP' then [Hard Credit Amt] else -[Hard Credit Amt]end)

               This does the same thin but you end up with a single measure

           

          Then you use a running sum or previous value to do the totals - (([net pledge jd])) +PREVIOUS_VALUE(0)

           

          I left you calculations in there for the chart -

           

          Let me know if this helped

          Jim

          1 of 1 people found this helpful
          • 2. Re: Finding an outstanding balance based on ID #s
            Amy Carrier

            Hi, Jim!

             

            Thanks for this! This gets me part of the way there. (I didn't do the best at explaining what I need, so that's my fault.) I need to be able to calculate the outstanding balance as a standalone measure, so I can then use it in other calculations - a colleague helped me figure that out.

             

            Cheers,

             

            amy

            • 3. Re: Finding an outstanding balance based on ID #s
              Jim Dehner

              Hi Amy here are 2 ways

              the first will give you the overall net          { FIXED :sum(if [Contrib Type]='NP' then [Hard Credit Amt] else -[Hard Credit Amt]end) }

               

              the second will give you the total by constituent ID      { FIXED [Constituent ID]:sum(if [Contrib Type]='NP' then [Hard Credit Amt] else -[Hard Credit Amt]end) }

               

               

              in the test data there is only one constituent so the value would not change