Incorrect average - days past due date

[rajany089]

IF(
Date([Request_Closed_At]) <= [Effective_SLA_Date],
0,
IF(
Date([Request_Closed_At]) > [Effective_SLA_Date],
(Interval([Request_Closed_At] - [Effective_SLA_Date], 'h') / 24),
0
)
) AS [Days Past Due Date]

The code is designed to calculate the number of days a request is past its due date, based on the comparison between two dates: Request_Closed_At (the date when the request was closed) and [Effective_SLA_Date] (the target SLA date). The logic uses conditional checks (IF) and date functions to determine and output the number of days past due. when i try to use formula avg(days past due date) i get incorrect answer is there a way to calculae the date difference 

[Qrishna]

i dont think you can calculate something on something thats being calculated in the field name, first define the new field and in the next step calculate the AVg

try below:

Load *

     , avg(days past due date) AS [Days Past Due Date - Avg] //if you want Avg calc in a new field

     //, avg(days past due date) AS [Days Past Due Date] //if you want Avg calc in the same field

Load [other field list]

     , IF(Date([Request_Closed_At]) <= [Effective_SLA_Date]

, 0

, IF(Date([Request_Closed_At]) > [Effective_SLA_Date]

      , (Interval([Request_Closed_At] - [Effective_SLA_Date]

  , 'h') / 24),0))    AS [Days Past Due Date]

From/Resident Tablepath/LoadedTable;