Calculating Blocked time on Issues using Flagged Field

JIRA Software has a Flagged field used on Kanban or Scrum board for making a visual indication of blocked work. I have started importing Flagged as a dimension and also import the Flagged change history.

How would I form a Measure that calculated the amount of time an issue was in the flagged status so that I can sum the total amount of time items are blocked?

How would I do a similar calculation so that I can calculate the amount of blocked time by status?

3 Likes

Guy,

You need to create a new custom calculated field using the Javascript for the solution.

These advanced settings create a new measure giving the number of days for the Impediment status for the completed cycles (when the Impediment flag was also removed from the issue):

[jira.customfield_days_flagged]
name = "Days Flagged Impediment"
data_type="decimal"
measure = true
javascript_code='''

var date_from = Date.parse(issue.fields.created);
var days_flagged = 0.0;
issue.changelog.histories.forEach(function(history){
    history.items.forEach(function(historyItem){
      if (historyItem.field == "Flagged") {
        to = historyItem.toString;
        from = historyItem.fromString;
        if(to.equals("Impediment")) {
          date_from = Date.parse(history.created);
        }
        if (from && from.equals("Impediment")) {
          date_to = Date.parse(history.created);
          days_flagged += (date_to - date_from) / 1000/ 60 /60 / 24 ;
        }
      }
    });
  });
if (days_flagged>0) issue.fields.customfield_days_flagged=days_flagged;
'''

Kindly,
Janis, eazyBI support

1 Like

@janis.plume i tried this but im not seeing the measure. do i need to do anything after i edit the advance settings?

You need to select this field from the data import options and run a new data import:


After the import, a new measure should be available in the data cube.

Kindly,
Janis, eazyBI support

1 Like

I got it to go but i am not seeing any data. Any thoughts @janis.plume ?

1 Like

Hi,

One possible reason is that there might be needed a full data re-import for that. Please, check if that might help.

As you seem to be using the “Flagged” dimension note that those issues which are in status “Impediment” might have no data. The calculation detects those issues having left this status. Please, try the report showing also the (none) member for the debugging:

Please, contact eazyBI support once you need further debugging of this solution.

Kindly,

1 Like

How can i format this field to show result as dd:hh?

Hi,

There is a standard option for very similar formatting (dd:hh: mm). For that, you need to convert the days to minutes and select the respective option:

Kindly,
Janis, eazyBI support

1 Like

Hello Janis,

I followed all the steps mentioned above, I am seeing the measures now being displayed in the custom measures options. However, even after doing full re-import, I don’t see the data for all Epics which are in “Flagged/Impediment”.

Also I would prefer to have the days since the issue was moved in blocked status when it is actually in the blocked status rather calculate afterwards. Can you please help in that? Currently I you mentioned in above posts that once it leaves the status, the fields will be populated, can we populate the days even when it is in blocked status?

2 Likes

Hello @janis.plume ,

Awaiting your input here, I tried a normal import & a full re-import also but still the values are not getting populated.

And also suggest how do I populate the days since the issue is blocked when in Blocked status itself.

Hi,

The Javascript code gives value for issues having at least one transition to “Impediment” and transition from “Impediment.” The result will not be populated if the issue is for the first time marked as Impediment.

This thread discusses the approach for calculating the days since the last change in the Flagged field: How long Issue was flagged to Paused (Cumulative) - #6 by janis.plume.
The solution can be used to calculate the days since Flagged as Impediment for the issues currently Flagged.

The calculation of the days since the issue is moved to status should not need the Javascript calculated custom field. The most straightforward way to have the time in current status is:

CASE WHEN 
[Measures].[Issue status]="Blocked"
THEN
DateDiffDays(
  [Measures].[Issue status updated date],
  Now()
)
END

Kindly,
Janis, eazyBI support

Hi Guys,

Can you please shed more light on the difference between those fields:

Many thanks,
Ela

Hi,

you should see the difference between those measures if you view them with the Time dimension. This section in the documentation explains that: Jira custom fields

Kindly,
Janis, eazyBI support

Thank you, that actually helped to understand the numbers.

The example @janis.plume shared earlier in this thread would work only when the new calculated field is defined from eazyBI advanced settings.

If you define your new calculated field from the import options page, the Javascript requires return
Then this is the right code to use:

var date_from = Date.parse(issue.fields.created);
var days_flagged = 0.0;
issue.changelog.histories.forEach(function(history){
    history.items.forEach(function(historyItem){
      if (historyItem.field == "Flagged") {
        to = historyItem.toString;
        from = historyItem.fromString;
        if(to.equals("Impediment")) {
          date_from = Date.parse(history.created);
        }
        if (from && from.equals("Impediment")) {
          date_to = Date.parse(history.created);
          days_flagged += (date_to - date_from) / 1000/ 60 /60 / 24 ;
        }
      }
    });
  });
if (days_flagged>0){return days_flagged;} 

Martins / eazyBI