Forum Discussion

MadhavDholakia's avatar
MadhavDholakia
Helper I
1 year ago
Solved

Average Ticket Closure Time

Hello,

 

I am working with ITSM Data Set where some of the columns are ID, Department, CreatedDate (DD-MM-YYYY HH:MM:SS), ClosedDate (DD-MM-YYYY HH:MM:SS) etc.

 

I would like to display Average Closure Time (i.e., Closed Date - Created Date) by Department in xday yhrs format in a matrix viz.

 

Thank you.

 

Regards,

Madhav 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi MadhavDholakia -

     

    create a calculated column or measure to compute the difference between ClosedDate and CreatedDate.

     

    now  create a measure as below:

    AvgClosureTime =
    VAR TotalSeconds = AVERAGE(ITSMData[ClosureTime])
    VAR Days = INT(TotalSeconds / 86400)  -- Number of days (1 day = 86400 seconds)
    VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)  -- Remaining hours (1 hour = 3600 seconds)
    RETURN
        FORMAT(Days, "0") & " days " & FORMAT(Hours, "0") & " hrs"

     

     

    Hope this works in your scenerio.

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi MadhavDholakia -

     

    create a calculated column or measure to compute the difference between ClosedDate and CreatedDate.

     

    now  create a measure as below:

    AvgClosureTime =
    VAR TotalSeconds = AVERAGE(ITSMData[ClosureTime])
    VAR Days = INT(TotalSeconds / 86400)  -- Number of days (1 day = 86400 seconds)
    VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)  -- Remaining hours (1 hour = 3600 seconds)
    RETURN
        FORMAT(Days, "0") & " days " & FORMAT(Hours, "0") & " hrs"

     

     

    Hope this works in your scenerio.