Metrics 'Time by state' for demands

[Leonel Sandroni]

I want to build an indicator that shows the average time (days or hours) that demands are in each state. The purpose is to identify bottlenecks in the demand's flow. Performance Analytics only have indicators with the entire time (from start to end) but it doesn't show by state.

[Amit Gujarathi]

Hi @Leonel Sandroni ,
To start, you'll need to create a Performance Analytics (PA) indicator that tracks the time spent in each state. You can use the PA formula builder to define the calculation for each state. Here's an example formula for the "In Progress" state:

(IF State = "In Progress" THEN 
(Timestamp.getDifference(gs.dateDiff(getEndTime(),getStartTime()),gs.dateDiff(getEndTime(),gs.nowDateTime()))) 
ELSE 0)

This formula calculates the time spent in the "In Progress" state by subtracting the start time from the end time and then subtracting the time elapsed from the current time if the demand is still in progress. You can create similar formulas for each state.

Next, you'll need to create a breakdown for the indicator by state. This will allow you to see the average time spent in each state separately. You can do this by going to the breakdowns tab in the indicator definition and adding a breakdown for the "State" field.

Finally, you can display the results of the indicator on a dashboard or report. You can use the "Average" aggregation function to display the average time spent in each state. Here's an example of how to display the average time in the "In Progress" state on a dashboard:

var avgTimeInProg = new GlideAggregate('pa_demand_indicator');
avgTimeInProg.addQuery('breakdown_label', '=', 'In Progress');
avgTimeInProg.addAggregate('AVG', 'calculation');
avgTimeInProg.query();
if(avgTimeInProg.next()){
  var avgTime = avgTimeInProg.getAggregate('AVG', 'calculation');
  gs.info('Average time in progress: ' + avgTime + ' days');
}