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

# Can't make chart for debit and credit

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

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

hi,

The first step is to link the three tables. As I understand Performainvoice is the ink between the three tables.

To find out Account Payable i.e the remaining amount to be paid i.e Total Amount (Sum of invoice amount) - Amount paid till date (Sum of payment amount)

to find out Receivable i.e The remaining goods/ amount the customer need to received is calculated from the Total Invoice Amount(Sum of invoice amount)  -  the Amount/good received(shippment amount)

I hope this is clear.

Deepak

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

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 Customer Proforma Invoice# Invoice Amount A 1 10000 A 2 14000 B 3 20000 B 4 30000

I need to get this:

 Customer Receivables Accounts payable A 2000 3500 B 3500 5000 Total 5500 8500

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

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

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

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)

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

Thank for help. I try it with my file, and understand, that I have only ID in my base. My PI,Shipment and Payments have ID as AA, AB, AC, AD etc. If I change PI# from numbers to ID, expression doesn't work. What should I do?

P.S. can't find, how attache XLS to message.

 Customer Proforma Invoice# Invoice Amount A AA 5000 A AB 7000 B AC 10000 B AD 15000

 Proforma Invoice# Payment# Payment Amount AA AA 2000 AA AB 3000 AB AC 1500 AB AD 2000 AC AE 5000 AC AF 5000 AD AG 7000 AD AI 3000

 Proforma Invoice# Shipment# Shipment Amount AA AA 1000 AA AB 2000 AB AC 3000 AB AD 4000 AC AE 1500 AC AF 5000 AD AG 8000 AD AI 7000
• ###### Re: Can't make chart for debit and credit

Change the expression function 'Min' to 'MinString' and 'Max' to 'MaxString'.

It wil work.

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

If I add one more PI# with customer A in table 1, it doesn't work. MaxString and MinString count 2 PI# only for each Customer.

Customer    Proforma Invoice#    Invoice Amount

A    AA    5000

A    AB    7000

B    AC    10000

A    AE    5000

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

It would be easy if you could provide all your requirement at the same time.

For the current requirement you can use the below expresion:

Payables:

=Sum(If(Aggr(Sum([Payment Amount]),[Proforma Invoice#])> Aggr(Sum([Shipment Amount]),[Proforma Invoice#])

, Aggr(Sum([Payment Amount]),[Proforma Invoice#])-Aggr(Sum([Shipment Amount]),[Proforma Invoice#]),0))

Recivables:

=Sum(If(Aggr(Sum([Shipment Amount]),[Proforma Invoice#])> Aggr(Sum([Payment Amount]),[Proforma Invoice#])

, Aggr(Sum([Shipment Amount]),[Proforma Invoice#])-Aggr(Sum([Payment Amount]),[Proforma Invoice#]),0))

Hope this helps.

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

Thank for your help. I will try to give my questions with right requirements.