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

Questions & Answers
,
1

hi,
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.
Thanks,
Vrukesh

2

Hi,

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:

[jira.customfield_11100]
separate_table=true
changes=true

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

image
image1666×408 30.1 KB

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

[jira.customfield_days_with_cf]
name="Days with T-shirt size"
data_type="decimal"
measure=true
multiple_dimensions=['Time','T-shirt size']
javascript_code='''
var t_history = new Array();
var datefrom = issue.fields.created;
var dateto=null;
var t_shirt = null;
	issue.changelog.histories.forEach(function(history){
	 history.items.forEach(function(historyItem){
	   if (historyItem.field == "T-shirt size") {
	      t_shirt = historyItem.fromString;
	      dateto = history.created;
	      if(t_shirt){
				  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:

image
image1608×310 27.7 KB

image
image1444×718 56.4 KB

Kindly,
Janis, eazyBI support

3

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.

Thanks,
Vrukesh

4

@vrukesh

Try creating new calculated measure using this formula:

CASE WHEN 
(
[Measures].[Transitions from issues count],
[Transition field].[T-shirt size]
) > 0 
THEN
[Measures].[Days with T-shirt size] 
/ 
(
[Measures].[Transitions from issues count],
[Transition field].[T-shirt size]
)
END

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