13 Replies Latest reply: Sep 23, 2012 1:41 PM by Max Pol RSS

    Can't make chart for debit and credit

    Max Pol

      I have three tables with numbers and amounts of invoices, payments and shipments. I can't understand, how get right result in the Chart? Should I prepare something in Script. I point yellow Chart that I need.

       

      Thanks for help.

        • Re: Can't make chart for debit and credit
          Deepak Kurup

          HI,

           

          Try the attach qvw file.

           

           

          Deepak

          • Re: Can't make chart for debit and credit

            Can u plz explain the logic for calculation Recievables and Accounts payable?

            • Re: Can't make chart for debit and credit
              Max Pol

              Thank for you help. My english is not good, but I try to explain, what is difficult for me. I write simple example, usualy I have different amount between PI, Payments and Shipment, and I can not use Proforma Invoice amout. So, if Table#1 is:

              Table#1
              CustomerProforma Invoice#Invoice Amount
              A110000
              A214000
              B320000
              B430000

               

              I need to get this:

              CustomerReceivablesAccounts payable
              A20003500
              B35005000
              Total55008500

               

              I should receive receivables and payables on the Customer in PI cut, but without Pivot Table.

                • Re: Can't make chart for debit and credit
                  Vigneswar Anantharam

                  Can you explain the logic used in terming Receivable & Payable?

                  Proforma Invoice# 1 - Used for Recivable for A

                  Proforma Invoice# 2 - Used for Payable for A

                  Proforma Invoice# 3 - Used for Recivable for B

                  Proforma Invoice# 4 - Used for Payable for B

                   

                  What is the logic to choose in Proforma Invoice#?

                   

                  1. Are all odd Invoice# Recivable and even Invoice# Paybale?

                  2. Or is it in any other table?

                   

                  I assumed option#1 and attached the QVW file.

                    • Re: Can't make chart for debit and credit
                      Max Pol

                      Logic is next. If Payment for Invoice more than Shipment - it's Payable. If Shipments more then Payable - it is Recivable. No more table.

                        • Re: Can't make chart for debit and credit
                          Vigneswar Anantharam

                          Dimension: Customer

                           

                          Expression:

                          Payable:

                          =If(Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Payment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Shipment Amount])

                                    >= 0,

                                    Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Payment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Shipment Amount]),

                                    Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Max([Proforma Invoice#]),Customer),','))}>}[Payment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Max([Proforma Invoice#]),Customer),','))}>}[Shipment Amount])

                          )

                           

                          Recivable:

                          =If(Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Payment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Shipment Amount])

                                    < 0,

                                    Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Shipment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Min([Proforma Invoice#]),Customer),','))}>}[Payment Amount]),

                                    Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Max([Proforma Invoice#]),Customer),','))}>}[Shipment Amount])

                                    - Sum({<[Proforma Invoice#] = {$(=Concat(Aggr(Max([Proforma Invoice#]),Customer),','))}>}[Payment Amount])

                          )

                           

                          Hope this helps.

                           

                          (File Attached)