# Calculating Blocked time on Issues using Flagged Field

**URL:** <https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044>\
**Category:** Questions & Answers\
**Tags:** jira, calculatedmembers\
**Created:** [September 21, 2018, 5:43pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044 "2018-09-21T17:43:02Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![guy.winterbotham](https://community.eazybi.com/user_avatar/community.eazybi.com/guy.winterbotham/32/13473_2.png) [@guy.winterbotham](https://community.eazybi.com/u/guy.winterbotham)\
**Post date:** [September 21, 2018, 5:43pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/1 "2018-09-21T17:43:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [October 3, 2018, 7:41am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/2 "2018-10-03T07:41:47Z")

</div>

Guy,

You need to create a new [custom calculated field using the Javascript](https://docs.eazybi.com/eazybijira/data-import/custom-fields/javascript-calculated-custom-fields) for the solution.

These [advanced settings](https://docs.eazybi.com/eazybijira/data-import/custom-fields/advanced-settings-for-custom-fields) 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

---

<div class="post-metadata">

**Author:** ![kleinj3](https://community.eazybi.com/letter_avatar_proxy/v4/letter/k/76d3ee/32.png) [@kleinj3](https://community.eazybi.com/u/kleinj3)\
**Post date:** [September 27, 2019, 4:28pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/3 "2019-09-27T16:28:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [September 30, 2019, 11:48am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/4 "2019-09-30T11:48:43Z")

</div>

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

 ![image](https://community.eazybi.com/uploads/default/original/2X/3/3e4006cfdc2f998b7d32a9a0226dd1eb62a4be8b.png)  
After the import, a new measure should be available in the data cube.  
 ![image](https://community.eazybi.com/uploads/default/original/2X/7/7f89f74650e7196144d2eeb910095e14eb20fa47.png)

Kindly,  
Janis, eazyBI support

---

<div class="post-metadata">

**Author:** ![kleinj3](https://community.eazybi.com/letter_avatar_proxy/v4/letter/k/76d3ee/32.png) [@kleinj3](https://community.eazybi.com/u/kleinj3)\
**Post date:** [October 1, 2019, 1:51pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/5 "2019-10-01T13:51:29Z")

</div>

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

 ![image](https://community.eazybi.com/uploads/default/original/2X/3/36602f47654cfde3b1787e6b12679583ac70f333.png)

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [October 7, 2019, 6:33am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/6 "2019-10-07T06:33:52Z")

</div>

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:

 ![image](https://community.eazybi.com/uploads/default/original/2X/e/ece341a2a7282f9a9f158e53df0cd08b78d5599f.png)

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

Kindly,

---

<div class="post-metadata">

**Author:** ![guinevere](https://community.eazybi.com/letter_avatar_proxy/v4/letter/g/898d66/32.png) [@guinevere](https://community.eazybi.com/u/guinevere)\
**Post date:** [February 23, 2021, 2:13am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/7 "2021-02-23T02:13:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [February 23, 2021, 7:53am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/8 "2021-02-23T07:53:12Z")

</div>

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:

 ![image](https://community.eazybi.com/uploads/default/original/2X/4/431481733ae175a82fbcc117f1fc4c62be6dc40b.png)

Kindly,  
Janis, eazyBI support

---

<div class="post-metadata">

**Author:** ![apanchal](https://community.eazybi.com/letter_avatar_proxy/v4/letter/a/3ab097/32.png) [@apanchal](https://community.eazybi.com/u/apanchal)\
**Post date:** [October 28, 2021, 10:48am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/10 "2021-10-28T10:48:30Z")

</div>

> [@guinevere](#):
>
> w result as dd:hh?

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”.

 ![Untitled](https://community.eazybi.com/uploads/default/original/2X/b/b4692e62e26fff576fb3265e1acab4abe593a7ea.jpeg)

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?

---

<div class="post-metadata">

**Author:** ![apanchal](https://community.eazybi.com/letter_avatar_proxy/v4/letter/a/3ab097/32.png) [@apanchal](https://community.eazybi.com/u/apanchal)\
**Post date:** [November 18, 2021, 11:02am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/11 "2021-11-18T11:02:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [November 23, 2021, 3:37pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/12 "2021-11-23T15:37:47Z")

</div>

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](https://community.eazybi.com/t/how-long-issue-was-flagged-to-paused-cumulative/6677/6).  
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:

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

```

Kindly,  
Janis, eazyBI support

---

<div class="post-metadata">

**Author:** ![Ela](https://community.eazybi.com/letter_avatar_proxy/v4/letter/e/a9adbd/32.png) [@Ela](https://community.eazybi.com/u/Ela)\
**Post date:** [November 8, 2022, 2:12pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/13 "2022-11-08T14:12:57Z")

</div>

Hi Guys,

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

 ![image](https://community.eazybi.com/uploads/default/original/2X/7/7ba3443727e6c85a46fc0b63b66fec26a42bdbb7.png)

Many thanks,  
Ela

---

<div class="post-metadata">

**Author:** ![janis.plume](https://community.eazybi.com/user_avatar/community.eazybi.com/janis.plume/32/8826_2.png) [@janis.plume](https://community.eazybi.com/u/janis.plume)\
**Post date:** [November 8, 2022, 3:21pm UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/14 "2022-11-08T15:21:42Z")

</div>

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](https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields#Jiracustomfields-Totalsofcustomnumberfields)

Kindly,  
Janis, eazyBI support

---

<div class="post-metadata">

**Author:** ![Ela](https://community.eazybi.com/letter_avatar_proxy/v4/letter/e/a9adbd/32.png) [@Ela](https://community.eazybi.com/u/Ela)\
**Post date:** [November 10, 2022, 9:49am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/15 "2022-11-10T09:49:34Z")

</div>

Thank you, that actually helped to understand the numbers.

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [December 2, 2022, 8:18am UTC](https://community.eazybi.com/t/calculating-blocked-time-on-issues-using-flagged-field/1044/16 "2022-12-02T08:18:07Z")

</div>

The example @janis.plume shared earlier in this thread would work only when the new calculated field is defined from eazyBI [advanced settings](https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/advanced-settings-for-custom-fields).

If you **define** your [new calculated field](https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/new-calculated-fields) from the import options page, the Javascript requires `return`  
Then this is the right code to use:

```auto
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