Can't make chart for debit and credit

[#1]

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.

[deepakk]

HI,

Try the attach qvw file.

Deepak

[#3]

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

[deepakk]

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

[#5]

Hope dis helps u..

[#6]

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.

[#7]

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.

[#8]

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

[#9]

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)

[#10]

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