Reporting on duration of status changes?

[L.Elias]

We have a custom lookup attribute on our idea object with various statuses. I now I have a request to report on and measure the amount of time ideas are staying in each status. My two initial thoughts are

1. Enable auditing on the attribute and reporting on that data. I found Report Audit trail Info which seems to be along the same lines but I haven't tried it yet.
   1. I don't like the idea of keeping perpetual audit trail data but not sure if that is a valid concern or not?
2. Create a slew of new attributes for each status and have a process that records the date the idea got set to the status on change, then report on those dates
   1. This just feels gross, not to mention if something ever goes back to a previous status, it'd lose the original date

Anyone else dealt with this sort of request before and have any pointers?

[Nick Darlington]

I would consider this as one of your options:

 

Create your own personal audit object as a subobject, and have a process insert records to the subobject whenever the change occurs.

 

Whilst it might seem like a lot of structure when there is already an existing audit trail, you don't have to worry so much about retentions and volumes when using a subobject, and unlike the per-status-attribute option, you can track multiple occurrences of the same status value / state.

[Deactivated User]

Just wondering again...

If you had a idea suboject which would have one status only and you would create a new instance every time the status changes the created_date field alone would allow you to get how long an idea stays in a status. That is because the created_date does not change even if the status did go backwards.

[Robert_Ensinger]

We're big into this. We keep duration in stage on our demand lifecycle, project lifecycle and issue lifecycle. We originally got off the ground with project lifecycle & datamarting out each night from the audit trail into a custom table - but this buries the data too far away from the users. We've since moved all of this to read only attributes on the appropriate objects and we love it. We date stamp the day something enters a stage then each night 'count up' business days in stage. Since this framework is on the object - anyone can access and use it. In 14.2 this'll be a checkbox away from being in the datamart. Not gross. powerful, accessible & easy. I actually think this capability should just be out of the box. If you cannot measure it, you cannot improve it.

[L.Elias]

So instead of having a datestamp attribute for each status start and end (like, "Eval Begin" and "Eval Finish") and calculating the dif, you have a day counter for each status (DaysInNew, DaysInEval, etc)? Hadn't thought of it from that angle. What if something changes twice within the same day, does it count a day in each or the first one gets missed?

 

Hmm...I suppose I can create an attribute for "last_status_change" or something, and each time the attribute changes have a process that increments the counter for the previous value by the dif between the current time and the new attribute (which it then resets to  current as well) rather than doing it once a night. Hadn't thought of that either.

[Robert_Ensinger]

Yep - Stamp the date when it enters the stage and a 'days' to count up business days. On our 'Version 1" of this we logged the change events to a table and did a diff. What we didn't like out of this is if one wants data - someone needs to write some code and it's a 'runtime' result. Before you write code - people need to tell you what they need and why. This can be a moving target.

 

By capturing this all as hard data on the object - anyone can use if for whatever they need. Below, I've merely added the columns to the project list to demonstrate.

It's real easy to aggregate:

For lifecycle 'stamping' I have an auto-start update process that looks for the stage change event:

...then stamp the appropriate Stage date and, if the days in stage is Null, writes a zero.

Each morning a job runs, evaluates if yesterday was a business day, looks at the stage then adds one to the appropriate days in stage. This gives us true 'business days in stage'.

- If something bounces though multiple stages in a day those will all be zero. This says something.

- If things bounce in and out of stages, we always stamp, effectively being last day it entered the stage. The count doesn't clear. If a request got kicked back to Scoping because the estimates needed redone - this was real elapsed time. The counter counts up from the existing days in stage count - need to see this.

 

Now that we can see the numbers, we need to put the process engine on our Demand Management process, set SLAs and manage the necessary processes.

Also, the hard numbers makes it easier to use in a process. We do all of this for On Hold projects, and the system sends notifications to the PMs every 30, 60, 90, 120 business days. I can find a number with the process engine's expression editor. I can't diff dates in the expression editor.

 

HTH