Calculated Field - Outstanding Invoice Count with date constraints

grace

Hi there - likely easy one here - I am trying to create a calculated field to count outstanding unpaid invoices that are either before their due dates or within 14 days of their due dates. When an invoice is unpaid, the "type" field is empty (i.e. no payment type.) I've also created a custom calculated column with the payment due date minus the current date. Each invoice has a unique invoice ID so I am essentially trying to count those. This is the count/case statement I have currently but I am not sure if a null value is working here.

Any thoughts?

count(case when `type`=NULL and when `Due Date MINUS Current Date`>=-14 then `invoice_id` else 0 end)

MarkSnodgrass

That formula won't validate as constructed. I would re-write it like this, assuming you have a due date field:

SUM(case when `type` IS NULL and DATEDIFF(`duedate`,CURRENT_DATE()) <=14 then 1 else 0 end)

This assumes type is actually null and not an empty string. I am using sum instead of count because we need to only count certain ones and we are using ones and zeros to do that. I'm assuming the due date is beyond the current date and so we are looking to see if it is within 14 days.

Hope this makes sense.