Options
Member
edited February 2022

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

• Coach
Options

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?

**Did this solve your problem? Accept it as a solution!**

• Contributor
Options

I think you want:

(case when `FirstSetupApprovedDate` is not null then `FirstSetupApprovedDate`-`CaseSubmissionDate` end)

(case

when `FirstSetupApprovedDate`is not null COUNT(`CaseID`)

end)

• Coach
Options

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?