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. 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. 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 1Ticket On Hold - Time 2Ticket Active - Time 3Ticket On Hold - Time 4Ticket Active - Time 5Ticket 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.

How can I calculate the total time a case spends in a particular set of states

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.

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.
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.

Page 1 / 1

Hi @stephen.humph , a similar question was posted by @lars.janse11 https://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.

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN