Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
HI,
Try the attach qvw file.
Deepak
Can u plz explain the logic for calculation Recievables and Accounts payable?
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
Hope dis helps u..
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.
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.
Logic is next. If Payment for Invoice more than Shipment - it's Payable. If Shipments more then Payable - it is Recivable. No more table.
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)
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 |