Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Ken_Hardwick
Contributor III
Contributor III

Calculating AR due dates

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

1 Reply
edwin
Specialist III
Specialist III

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.