IF Condition Query

[#1]

Dear All,

The Below condition is not working on my application, Can any one help me.

=if(num([Cust_Ledger_Entry.Posting_Date]+ Customer.Payment_Terms) > Customer.Payment_Terms,Detailed_Cust_Ledg_Entry.Amount)

Regards,

Antony.

[#2]

can u please attach example.

I think it's because of num(date+date) > date

[ToniKautto]

In addition to attaching the QVW file, please also clarify what the observed and expected result should be.

[chris_johnson]

Hi,

Just looking at the formula it looks like the expression will always be true and return the customer ledger amount. You have Customer.Payment_Terms on both sides of the '>' so the both sides of the expression will always start as equal before you've even added the Customer Ledger Posting Date.

What are you trying to achieve? Are you trying to get some sort of overdue balance total? If so then you maybe re-work it to something like:

if(num([Cust_Ledger_Entry.Posting_Date]+ Customer.Payment_Terms) < today(0), Detailed_Cust_Ledg_Entry.Amount)

Also, by any chance are you trying to get this out of a Nav database?

Regards,

Chris

[#5]

The information what we require here is to find the Overdue amount.

Posting Date is nothing but the Invoice Date. Payment Terms is the Credit Period provided to the Customer. Here Posting Date is fetched from Invoice Table and the Credit Days/period is fetched from Customer Master table. We need to find out the overdue amount. For example, if the Posting Date of an invoice is 01st March 2011 and the Credit Period provided for the invoice is 30 days, then the invoice is due on 31st March 2011. So we need to find out the sum of invoice amounts which are overdue.

At present, we may not be able to enclose the QVW file in this message. If you could provide us with the expression, it would be helpful to us.

[chris_johnson]

Just to modify my expression I did before:

=sum(if(num([Cust_Ledger_Entry.Posting_Date]+ Customer.Payment_Terms) < today(0), Detailed_Cust_Ledg_Entry.Amount))

I guess you'd want to sum the Detailed Customer Ledger Entries as you'd need to take into account any payments and credit notes.

Chris

[#7]

Hi Chris

Yes. You are right. We are trying to do this with Nav database.

Regards

Napolean

[chris_johnson]

Hi,

Did you try out the second formula I suggested? What was the result?

Chris

[#9]

Hi Chris

It worked. We tried your second formula only. Thanks for your help. Meanwhile, we have one more query.

We would like to find out the ageing of invoices - customer wise. A Customer can have multiple invoices at different dates and there could be many payments (which may not be directly related to the invoices).

For example consider the following:

Customer Name: ABC CORP

Invoice No. Invoice Date: Credit Days Due Date Amount

INV001 01/01/2011 30 Days 01/31/2011 USD 1000.00

INV002 02/01/2011 30 Days 03/04/2011 USD 1200.00

INV003 04/01/2011 30 Days 05/01/2011 USD 900.00

Now there is a payment made which is not specific to any invoice. The payment date here is not relevant. Paid amount is say USD 1200.00.

My condition is, first the payment should knock off the Amount of Invoices on FIFO basis. i.e., in this case, the payment will knock of the first invoice and it should not be taken into account at all. After knocking off the first invoice, there will be an amount of USD 200.00 still remaining. This should be taken into consideration for the next invoice. So, in this case, now the outstanding amount on the INV002 will be only USD 1000.00 after knocking off USD 200.00.

Now…the ageing should display USD 1000.00 under 60-90 days bucket and the amount related to INV003 should get displayed under 0-30 days bucket.

Can you please help us in this situation on how this can be done in qlikview.

Thanks

Napolean

[deepakk]

hi,

Chekc out the below forum for knockout method.

http://community.qlik.com/forums/t/27754.aspx

I had faced similar issue in my early days.. and valera had helped me out in the same.