Hello,
I need to add a measure with issues created and solved in the same period to the report:
I found a similar measure in some base reports:
Sum(
Filter(
-- iterate through set of issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember) AND
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember)
),
-- numeric expression - sum of relevant issues
[Measures].[Issues created]
))
But when I pasted it into the report, I got a timeout error:
Then I tried to make it simpler, but also encountered the same error:
Sum(
Filter(
-- iterate through set of issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember)
),
-- numeric expression - sum of relevant issues
[Measures].[Issues resolved]
))
Is there any way to make this measure faster? Maybe somehow with a tuple or Javascript custom field?
Thanks in advance
Hi @michailgoruynov,
There is not much room for optimization. You may add some filter criteria by issue properties like Sorted Priority matches the priority on report rows.
CoalesceEmpty([Measures].[Issue sorted priority],"") = [Sorted Priority].CurrentMember.Name
The custom calcaution is calculated for each report cell. You might want to reduce the report row and/or column count by removing less important information. For example, “All Sorted Priority”.
In the case of JavaScript calculated field, you can create it specific for a specific level: year, month, or week. For example, you can create a calculated custom field to count issue created and resolved in the same month. The calcauted custom field might look like in the picture below.
Here is the used JavaScrip code:
if (issue.fields.resolutiondate &&
//and the year and month of both dates are the same
issue.fields.created.substr(0,7)==issue.fields.resolutiondate.substr(0,7) ) {
//then count issue as created
return 1
}
More details on calculated fields and how to define them are described here: New calculated fields
Best,
Zane / support@eazyBI.com
Hi, @zane.baranovska
Thank you for your help
About the Sorted Priority I didn’t get your idea, sorry(. The Sorted priority in this report is only a member of the Current Priority dimension (a Javascript calculated field to get the current issue Priority without considering the value change history:
issue.fields.customfield_currentpriority = issue.fields.priority
) to sort values in the order I want and has a formula:
Aggregate(
{
[Current Priority].[ASAP],
[Current Priority].[Top task],
[Current Priority].[Highest],
[Current Priority].[High],
[Current Priority].[Medium],
[Current Priority].[Low],
[Current Priority].[Lowest]
}
)
I even tried to remove this member Sorted priority from the report, but I was still getting this error.
Right now there is only one filter in my report - Hide empty rows
About Javascript calculated field: I thought about it, but in case the user selects a different period in Pages - this measure woun’t work I think. Right?
The JavaScript calculated measure would work as long as users would run reports on a monthly view and select any period representing a calendar month, like 2022 Nov, 2023 May, Previous month. The calcaution won’t be accurate if you switch to the weekly view.
Regarding calcauted measure – the idea is to add more criteria to the formula part with Filter(). If you have a dimension, “Sorted priority” on report rows, you can hardcode this dimension into the calculation.
Sum(
Filter(
-- iterate through set of issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember) AND
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember) AND
--check if issue property value matches the sorted priority name on report rows
CoalesceEmpty([Measures].[Issue sorted priority],"") =
[Sorted Priority].CurrentMember.Name
),
-- numeric expression - sum of relevant issues
[Measures].[Issues created]
))
@zane.baranovska
I wrote this formula because Sorted priority is only a member of Current Priority an there is no measure or property for it:
Sum(
Filter(
-- iterate through set of issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember) AND
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember) AND
CoalesceEmpty([Measures].[Issue Current Priority],"") =
[Current Priority].CurrentMember.Name
),
-- numeric expression - sum of relevant issues
[Measures].[Issues created]
))
I ended up getting the same error as usual - Timeout
It turns out that Javascript custom field is the only option for me to get the report to work? Or did I do something wrong with the formula?
@zane.baranovska, Hi
In addition to the previous post: I have written a Javascript calculated measure following your example and I get the timeout error when I’m trying to use Drill through Issue function 
When you import the JavaScript calculated field, you should use one of the created measures directly in the report. For example, pick the measure “Created and resolved in same month resolved” measure.
I use the single Created and resolved in the same period created measure in the report and there’s always the timeout error.
This is my Javascript code:
#adds the measure for Issues created and resolved for the same period;
[jira.customfield_createdresolvedinmonth]
name = "Created and resolved in the same period"
data_type = "integer"
measure = true
javascript_code = '''
if (issue.fields.resolutiondate &&
//and the year and month of both dates are the same
issue.fields.created.substr(0,7)==issue.fields.resolutiondate.substr(0,7) ) {
//then count issue as created
issue.fields.customfield_createdresolvedinmonth = 1;
}
'''
@zane.baranovska Please help with this problem. It is very important to be able to open the task list(
@zane.baranovska, Hi
I found that I only get the error only after adding a custom member to the Pages like this:
Aggregate(
Except(
[Products].[Products].Members,
{[Products].[1],
[Products].[2],
[Products].[3]}
)
)
Maybe there are other options to exclude some values in a Dimension’s list with a large number of values?
Hi @michailgoruynov,
Aggregated members in other dimensions like “Products” affects the report performance as eazyBI has to check on all products for each report cell and also when drilling through issues.
There are options to group Products more efficiently.
-
If Product is a single select custom field in Jira, you can add new hierarchy to group products by some property.
Here are more details on custom hierarchies: https://docs.eazybi.com/eazybi/analyze-and-visualize/custom-hierarchies
-
Another option is to create a new dimension during data import with JavaScript calcauted custom fields. For example, “Product category”.
The JavaScript should sort based on a Product name and return the hardcoded value that would group them.
Best,
Zane / support@eazyBI.com
Hi @zane.baranovska
Unfortunately Products is a multiple select custom field. So I used your second tip with creating Javascript calculated custom field. Thank you very much, everything works great!
But if had a single select field, could you help me understand what this new hierarchy looks like for grouping products with some example?
I also found this kind of syntax:
[Products].[All Products] - [Products].[1]
Is this also correct?
@zane.baranovska Hi, I’m terrible sorry, but could you help me one last time in this thread with the last two different questions in the post above?
Thanks you in advance.
Hi @michailgoruynov,
If the Product is the multi-selection custom field, then the custo hierarchy is not supported. Sorting issues by Product categories should be described in the JavaScript for the calcauted field.
I would not recommend using the expression [Products].[All Products] - [Products].[1] as it does not align with our recommendations on how to build calculated members. Here are the ground rules: Calculated measures and members.
However, you can slightly modify the expression and create a new calculated measure in the Measures section. Add a measure, like “Issue created count,” “Issue resolved count,” or some predefined measure to correct the expression.
--count of issues across all Products (DefaultMember)
([Measures].[Issues created count],
[Products].CurrentHierarchy.DefaultMember)
-
--subtract count of issues matching specified product "1"
([Measures].[Issues created count],
[Products].[1])
Hi, @zane.baranovska
Your example refers to Calculated measure, but my formula is used to calculate Calculated member, and it works much faster than using the standard Except function. Why can’t this formula be used
Mathematical operations are not supported for calcauted members as those might give unpredictable results for some reports. While it works fine for your particular report where you are looking for created issue count, it might give invalid results if you choose some other metrics and add a Time dimension to filter report data.
1 Like
Do you have any plans to speed up the Except function? To be able to use Calculated member as a filter in Pages to make the report more flexible and use Drill through issue?
