Skip to main content

We have a ServiceNow analysis and the business would like to know how long tickets are spending in a series of states. We are trying to analyse if we are putting calls on hold for too long.

 

We are looking to do two things.

 

  1. For any set of tickets give me the total time this ticket has spent in a particular activity. So the event time of this activity until the next activity. The ticket may be in this state more than once through it's process journey. There are more than one of these states.
  2. Report on any tickets that have exceeded a specific threshold of time in one of these states.

 

So the flow might go

 

Ticket Created - Time 1

Ticket On Hold - Time 2

Ticket Active - Time 3

Ticket On Hold - Time 4

Ticket Active - Time 5

Ticket Resolved - Time 6

 

I'm looking for the total time between Time 2 & Time 3 added to the time between Time 4 and Time 5.

 

I cannot see a way to do this.

 

Any ideas greatly appreciated.

 

 

Hi @stephen.humph , a similar question was posted by @lars.janse11https://www.celopeers.com/s/question/0D50700000XkKX2CAN/throughput-time-based-on-activity-index

 

Probably the best approach is transform the data in the event collection via sql.

 

Good Luck!!

Thanks I see. It's similar but not the same in that I don't know which activity will follow, it could be one of many. I'll have a look via SQL. It would be useful if it were possible via the analysis. It's essentially find occurrence of activity, now find the next activity, subtract the timestamp of the first activity from the next one, repeat if it happens more than once.

Hey Stephen,

 

I think this should be achievable with a combination of SOURCE() TARGET() and a CASE WHEN....

 

something like ...

 

 

AVG(

CASE

WHEN SOURCE(<activity_column>) = 'Ticket On Hold '

THEN DAYS_BETWEEN ( SOURCE(<activity_column>), TARGET(<activity_column>))

ELSE NULL

END

)

 

Cheers :-)

Lars

Hi Stephen,

 

We had a very similar requirement recently. We made use of ACTIVITY_LEAD.

 

The below was for total time in each state:

 

DIMENSION:

CASE

  WHEN ACTIVITY_LEAD ( "_CEL_INC_ACTIVITIES"."ACTIVITY_EN" , 1 ) != 'Closed' THEN "_CEL_INC_ACTIVITIES"."ACTIVITY_EN"

  ELSE

    NULL

END

 

KPI: SUM(CASE WHEN ACTIVITY_LEAD ( "_CEL_INC_ACTIVITIES"."ACTIVITY_EN",1 ) != 'Closed' and process equals 'ON-HOLD Awaiting Vendor' THEN

 ( DATEDIFF ( dd , "_CEL_INC_ACTIVITIES"."EVENTTIME" , ACTIVITY_LEAD ( "_CEL_INC_ACTIVITIES"."EVENTTIME" , 1 ) ) )

ELSE NULL END)

 

COMPONENT FILTER: FILTER "_CEL_INC_ACTIVITIES"."ACTIVITY_EN" != 'Closed';

Thanks Lars and David, I shall certainly give these a go and see how I get on. Very timely responses, I have a workshop with the business stakeholders tomorrow, so getting this in prior to that would be a great bonus!

Thanks both, these both do what I need and seem so simple when you look at them.

Community Code of ConductAccessibility statement