RITM Stage average duration

[AndreeaT0081691]

Hi everyone,

I’m trying to calculate the monthly average duration that Closed Complete RITMs spend across 3 stages:

• Asset Selection

• Asset Preparation

• Asset Shipment

Basically: from start of Asset Selection to end of Asset Shipment.

What I’ve done so far:

• Created a RITM Stage metric.

• Built a database view between metric_instance and sc_req_item.

• Indicator source is on the view with:

  • metric_definition = Stage

  • value = Asset Selection OR Asset Preparation OR Asset Shipment

  • state = Closed Complete

So far so good.

Where I’m stuck:

For the automated indicator, I’m using this source and setting Aggregation = Average (Scripted). I’m supposed to write a script that calculates the duration from metric start to metric end. 

Has anyone done this with a scripted aggregate before?
Could you share an example script, or suggest a better approach (still without adding fields to without creating custom fields)?

Thanks!