Average Lead Time
Hello, I am trying to get the average of lead time between submitted and approved
this is my formula but is showing invalid formula
when `FirstSetupApprovedDate` is not null then `FirstSetupApprovedDate`-`CaseSubmissionDate`
/
case
when `FirstSetupApprovedDate`is not null COUNT(`CaseID`)
end
Best Answer
-
Hi @dylang91
How are your numbers weird?
IFNULL returns the second parameter if the first one is NULL. This means you're subtracting NULL -
CaseSubmissionDate
which equates to null. NULL / Number is also NULL.What you'd likely want is something like this where you're calculating the value if it's not null:
AVG(`FirstSetupApprovedDate`-`CaseSubmissionDate`)
AVG excludes any records which are NULL from the calculation and if FirstSetupApprovedDate is null then your arithmetic operation equates to NULL.
I'm not sure what you're trying to do with CaseID in this formula. What does CaseID represent?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Your syntax in your numerator is missing the "case" and "end".
I think you want:
(case when `FirstSetupApprovedDate` is not null then
FirstSetupApprovedDate`-`CaseSubmissionDate
end)/
(case
when `FirstSetupApprovedDate`is not null COUNT(`CaseID`)
end)
0 -
Hi @dylang91
How are your numbers weird?
IFNULL returns the second parameter if the first one is NULL. This means you're subtracting NULL -
CaseSubmissionDate
which equates to null. NULL / Number is also NULL.What you'd likely want is something like this where you're calculating the value if it's not null:
AVG(`FirstSetupApprovedDate`-`CaseSubmissionDate`)
AVG excludes any records which are NULL from the calculation and if FirstSetupApprovedDate is null then your arithmetic operation equates to NULL.
I'm not sure what you're trying to do with CaseID in this formula. What does CaseID represent?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith thank you I didn't know that average will exclude the null.
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
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive