Is there a report that shows all Sales Orders that are past due by the # of days?

[chris-macdonald]

We tried creating a Sales Order report in the Raw Data Explorer. We filtered on the following:
- Order Status = Invoiced
- Payment Status = Unpaid

We added the following fields:

• Issue Date
• Amount Due
• Due Date
• Invoice Due Date

Issue We don’t see a way to filter on Orders that have not been paid past the Due Date, or how long over due they are.

Solution Can you add the XX Days Over Due value from the Customer → Sales orders & credit notes page?

I understand you can export the report into excel, and then manually add a formula, but we feel that this is a redundant step. Thanks!

[luke]

This can be done in the Raw Data Explorer, although I appreciate that the date filters can be a bit confusing at first.

To filter on invoices due over 30 days ago, for example, you could use:

due_date < "30 days ago"

We also have a guide on date filtering which you might find useful.

You can combine this with your other filters to:

order_status = "Invoiced" and payment_status != "Paid" and due_date < "30 days ago"

Note, that instead of payment_status = "Unpaid", I have used payment_status != "Paid". This is because we have another status of “Part paid” which if you have any invoices in this status are probably useful to see. The != means “not equal to”, so “show me invoices not paid” is slightly more encompassing than “show me unpaid invoices”. I hope this makes sense and is useful.

I hope this covers what you’re looking for?

[chris-macdonald]

Thanks for the help Luke!

This is great advice and super helpful.

I am wondering how to handle scenarios where the Payment Terms are not 30 Days.

For example, sometimes they are 14 days, 60 days, 90 Days, etc…

I see an “Invoice Due Date” field, but that is just a numeric number (30, 60) not an actual date.

Am I missing an actual “DUE DATE” field that is in a date format?

If not, then maybe the workaround is to create multiple reports:

• One for 30 Day Payment Terms with a hard filter of 30 Days
• One for 60 Day Payment Terms with a hard filter of 60 Days
  etc…

Again, thanks for the help!

[luke]

Ah yes, again this is slightly confusing, to be honest…

The “Invoices Due Date” field is the number that goes in conjunction with the “Invoice Due Date Option”. These fields match up with the fields you see when adding/editing a customer to define their payment terms, but like you say they tell you how the date was calculated, but not the actual date. The “Invoice Due Date Option” field looks like the below screenshot, which hopefully helps explain it a bit better.

---

In your case, the date formatted field for the actual payment due date for any given invoice is the one named just “Due Date”, this one should be usable with a relative date to today (or a specific date), like below:

due_date < "30 days ago"

or

due_date < "09/02/2022"

That should do what you need, but do please let us know if you’ve any trouble! Cheers.

[chris-macdonald]

Thanks so much for the help @luke !

We were able to create individual reports for each payment term, so the team can now check these reports to see what customers are Past Due.

I have a similar question about payment date, but will add it to a new topic.

Appreciate all the help!

[joost-allema]

Hello Luke,

Came across this topic as I am building a similar report, but I get an error message when I apply these filters:
order_status = “Invoiced” and payment_status != “Paid” and due_date < “30 days ago”
The error message is: There was a problem with the BrewwQL. Please double-check that it is valid. We could not parse the date or relative time of ‘30 days ago’.

What should or can I adjust to make this work?

[luke]

Thanks for the question, Joost, and sorry for the confusion.

In Breww date formatting/parsing is aware of your location and should be formatted/translated for your language (even though most of Breww is still only in English). You should be able to use the “30 days ago” part in Dutch, like this:

order_status = "Invoiced" and payment_status != "Paid" and due_date < "30 dagen geleden"

I hope this helps!

[joost-allema]

Thanks Luke, It works!
And good to know about the language and location.