Calculating AR due dates

[Ken_Hardwick]

Hello,

I am looking for some help on how to tackle an accounts receivable problem. Our ERP system holds the invoice date and terms for our accounts receivable invoices but does not hold a due date. This is always calculated as needed in the ERP.

Some of our terms are easy like 30 days from invoice and this would be easy to report on in Qlik. Where I do not know how to tackle this problem is with end of month terms (EOM). For example 30 days EOM, 60 days EOM etc.

How can I get Qlik to look at an invoice date, look at the terms applicable to the invoice and if EOM terms, apply the necessary days from the end of the month to get the correct due date? Is this something to do in the load script or with set analysis?

Thanks

[edwin]

the terms of an agreement does not change, so you can build the logic in your script.  make sure that you conform all the contracts to a single rule like days after end of month (of invoice).  so you have a table that links to your invoices (i assume by unique companyID) and hold the number of daysEOM:

CONTRACTS:
CompanyID, daysEOM

left join this to your invoices, then create a calculated field:

=date(monthend(InvoiceDate) + daysEOM)

in reality contracts have start and end dates and if that is true, its a more complicated logic.  however, if the invoice references a contract then that may be your link instead of companyID.  that said the solution suggested is wil change based on what data i s available and how the contract is linked.