Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

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.

13 Replies
deepakk
Valued Contributor III

Re: Can't make chart for debit and credit

HI,

Try the attach qvw file.

Deepak

Not applicable

Re: Can't make chart for debit and credit

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

deepakk
Valued Contributor III

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

Not applicable

Re: Can't make chart for debit and credit

Hope dis helps u..

Not applicable

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
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.

Not applicable

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.

Not applicable

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.

Not applicable

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)

Not applicable

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.

CustomerProforma Invoice#Invoice Amount
AAA5000
AAB7000
BAC10000
BAD15000

Proforma Invoice#Payment#Payment Amount
AAAA2000
AAAB3000
ABAC1500
ABAD2000
ACAE5000
ACAF5000
ADAG7000
ADAI3000

Proforma Invoice#Shipment#Shipment Amount
AAAA1000
AAAB2000
ABAC3000
ABAD4000
ACAE1500
ACAF5000
ADAG8000
ADAI7000
Community Browser