Data Visualization of Duration in Demands

[KlydeD]

Hello everyone, so I've been tasked with creating a data visualization for work which is looking for the average duration time(calendar_duration) it took for a demand to be closed using Submitted on(submitted_date) and Closed (closed_at).

I was thinking maybe fixing the business rule for Duration(calendar_duration) or just calculating it by using performance indicators? The problem I'm having with performance indicators is that submitted on(submitted_date) uses date value while closed(closed_at) uses date/time.

Please help.

[pratikjagtap]

Hi @KlydeD ,

try below steps 

• 1.Fix the Business Rule for calendar_duration
  -If calendar_duration is already calculated within the system, ensure that the business rule correctly considers both submitted_date and closed_at.
  -Check if it's accounting for time differences properly (including handling null or missing values).
• 2.Using Performance Indicators
  -Since submitted_date is a date and closed_at is a date-time, ensure you're normalizing both before performing calculations.
  -Convert submitted_date to a date-time format (e.g., assuming the start of the day 00:00:00).
  -Compute the difference between closed_at and submitted_date using a formula like:

            DATEDIFF(HOUR, submitted_date, closed_at)/24 -- To get days as a decimal
           -Alternatively, if you need whole days: 

          DATEDIFF(DAY, submitted_date, closed_at)
3.Validation Steps

• Identify discrepancies where calendar_duration does not match the calculated difference.

• Check if the business rule excludes weekends or working hours (if needed).

• Ensure that closed_at is never null before calculating differences.

If this solution helps you then, mark it as accepted solution ‌‌✔️ and give thumbs up 👍 !

Regards,

Pratik

[KlydeD]

What about if I need a data visualization for average duration time of completed demands which is calculated now using Created(sys_created_on) and Closed (Closed_at)? The way I've set this up is by formula indicator > 2 automated indicators (1 for duration of demands, 1 for number of resolved incidents) and which we will also have an indicator source and a data collector job. I tried copying the OOB Incident MTTR format but I'm getting errors on my data collector job when I execute it.