Calculated Field - Outstanding Invoice Count with date constraints

grace
grace Member
in Magic ETL

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)

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited March 2022

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • grace
    grace Member

    Thank you! This worked :)

Categories