Calculate "Average number of days for transition" for custom field

We are using custom field which has values like “New”, “Verified”, “Awaiting User Input”, “Closed” etc.

We want to check the average days taken for transition from any of these states to any other state. For example: New → Closed, New → “Awaiting User Input”, “Awaiting User Input” → Closed, etc.

We have already added setting mentioned at: Advanced settings for custom fields

Need further help to calculate average days for transition between values of this custom field.


The solution is possible with a Javascript calculated custom field.
I have the custom field “T-shirt size” (11100) and applied the following advanced settings to it to enable the value change import for it:


I also need to select the data import of this field, including the value changes:

The following advanced settings create a new measure for calculation of the time issues spent with each custom field value:

name="Days with T-shirt size"
multiple_dimensions=['Time','T-shirt size']
var t_history = new Array();
var datefrom = issue.fields.created;
var dateto=null;
var t_shirt = null;
	   if (historyItem.field == "T-shirt size") {
	      t_shirt = historyItem.fromString;
	      dateto = history.created;
				  duration = (Date.parse(dateto) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
				  t_history.push(dateto.toString().substr(0,10) + "," + t_shirt + "," + duration);
			datefrom = dateto;
			t_shirt = historyItem.toString;
if (t_history) issue.fields.customfield_days_with_cf=t_history.join("\n");

Now, I import this field as a measure and can see for how long each issue spent in each t-shirt size:

Janis, eazyBI support

Hi Janis,

Sorry for delay. IT implementation at back-end took a little while.

We could implement the suggestion by you!

a. Right now, it correctly shows time spent by an issue in L, M, XXL. If we select all issues, it shows total sum for days. How can we get average days spent in L, M, XXL? Please consider that each issue may not have each of states, for example, some issue might have only L and M; and not XXL.

b. For each issue, while it shows the total days in L, M, XXL; can we show how much total days does it spend in each of those sizes before moving to other? For example:
L-> M: 2.0 days
L-> XXL: 3.0 days
M->L: 10.0 days
M->XXL: 11.0 days

and so on.



Try creating new calculated measure using this formula:

[Measures].[Transitions from issues count],
[Transition field].[T-shirt size]
) > 0 
[Measures].[Days with T-shirt size] 
[Measures].[Transitions from issues count],
[Transition field].[T-shirt size]

It would calculate the average days with t-shirt size and you can use it with your current Tshirt size dimension already, but it won’t split L>M and L>XXL as in your example
Instead, it will return L: 2.5days

Martins / eazyBI