Calculated Field - Outstanding Invoice Count with date constraints
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
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
Thank you! This worked :)
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 713 Beast Mode
- 50 App Studio
- 39 Variables
- 669 Automate
- 170 Apps
- 447 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive