Average Lead Time

dylang91
dylang91 Member
edited February 2022 in Magic ETL

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

  • GrantSmith
    GrantSmith Coach
    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!**

Answers

  • mhouston
    mhouston Contributor

    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)

  • GrantSmith
    GrantSmith Coach
    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!**
  • dylang91
    dylang91 Member

    @GrantSmith thank you I didn't know that average will exclude the null.

Categories