Sum of ROM (Days) Are Not Completed from Previous Month vs Current Month

Questions & Answers
, , , ,
1

Hi,
Can someone please advise how to build MDX code to sum up a custom number field called ROM (Days) that are not done/completed from the previous month and another for the current month. I can’t use [Measures].[ROM (Days) created] because it’d only specify what was created in a given month vs. the entire universe of ROM (Days) currently outstanding. I tried with this code:

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
NOT (
[Measures].[Issue status] MATCHES ‘Done|Canceled|Deferred’
)
),
(
[ROM (Days)],
[Time].[Current month]
)
)

Any help is appreciated, thanks

2

Hi @Keiko

In this case, I would recommend using historical statuses and tuples.
First, make sure that “ROM (Days)” numeric field is imported with change history.

Then use this formula for calculated measure:

[Measures].[ROM (Days) history]
-
Aggregate({
  [Transition Status].[Done],
  [Transition Status].[Canceled],
  [Transition Status].[Deferred]
  },
  [Measures].[ROM (Days) history]
 )

It will work with your calculated Time dimension periods and show the historical sum (at the end of particular period) of ROM (Days) for issues were not in the closed statuses at the end of each period.

Martins / eazyBI

3

Thanks Martins, this is super helpful!

4

Hi @martins.vanags

I have a separate question in regarding Original Estimates instead of ROM (Days) but for the same concept as above. If I want to see sum of Original Estimates that were in the status of “Done” at a particular month, I utilized per your suggested format:

Aggregate({
[Transition Status].[Done]
},
[Measures].[Original estimated hours]
)

However, it doesn’t show any data when it should. The time dimension is set in Month.

If I have it as the formula below, the data shows, but it’s showing current status instead of the status at the time:

(
[Measures].[Original estimated hours],
[Status].[Done]
)

Can you please assist the appropriate formula? Thank you, much appreciated!

5

“Original estimated hours” are not imported with the history by default.
Here is how to create a scripted field in Jira Data Center to retrieve original estimated hours history values for eazyBI

You can import this scripted field in eazyBI as a new numeric measure and use it in your formula.

If you use eazyBI for Jira Cloud, you can define the original estimated hours history field using this definition from eazyBI advanced settings, then import it as new measure from the import options page

[jira.customfield_or_est_h_hist]
name = "Original estimated hours history"
data_type = "decimal"
measure = true
multiple_dimensions = ["Time"]
javascript_code = '''
if (issue.changelog && issue.changelog.histories) {
  var ortimeEstimateHistory = [];
  for (var i=0; i < issue.changelog.histories.length; i++){
    var history = issue.changelog.histories[i];
    for (var a=0; a < history.items.length; a++) {
      var item = history.items[a];
      if (item.field == "timeoriginalestimate") {
        ortimeEstimateHistory.push(history.created.substring(0, 10) + ',' + parseInt(item.to?item.to:"0")/3600);
      }
    }
  }
 return ortimeEstimateHistory.join("\n");
}
'''

Martins / eazyBI

6

Hi @martins.vanags
I went to Settings → Advanced Settings and pasted the code but looks like it had a “unterminated string at line 19 column 2” error:

originalesteazybi
originalesteazybi1006×811 68.6 KB

7

Hi @Keiko
One (last) line was missing in the code earlier.
I just updated the code.

Martins / eazyBI

8

Thanks @martins.vanags
It went through, but then when I imported the custom field, it errored out saying “too many issue import errors.” I put in a support ticket there if you need to dive in a little deeper into our system.

9

The previous code didn’t handle cases where the original estimate was deleted. Those cases in eazyBI change history have to be replaced with 0 value
I just updated the code again.

Martins / eazyBI

1 Like
10

Thank you @martins.vanags looks like it went back to normal, and I can now see the Original estimated hour history.

However, when I try to create a measure called “Completed Estimated Hour” with the formula below:

Aggregate({
[Transition Status].[Done]
},
[Measures].[Original estimated hour history]
)

It doesn’t show any data. Can you please help me understand why? The report is called "OPS Open & Completed Hours (Monthly)

I’m replacing it with:
(
[Measures].[Original estimated hours],
[Status].[Done]
)
But this only shows the current status of what was created at that month