I have created a graphical representation. of Jira tickets created, open and closed. but in report in Oct month, we created 262 tickets but in Open issues it shows -200 and in closed Issues it shows 462 tickets.
why I am getting negative value and how can I make it to positive value and also when I drill through the issues it showing closed tickets not opened tickets.
I want to get the data of only that particular month in October it should show only Oct month created tickets and Open and closed. It should not show previous month created tickets closed in this month.
Thanks!
Hello,
It looks like in your report, “Issues Open” is calculated by the formula = “Issues created” - “Issues closed”.
This can give negative results if more issues are closed than are created, because:
“Issues created” - A total number of created issues in the specified time period (issue created date is in the selected time period).
“Issues closed” - A total number of closed issues in the specified time period (issue closed date is in the selected time period, but they can be created anytime before)
However, to get the total number of issues that are created and closed in the same month, you can create a new measure and use this formula:
NonZero(Sum(
Filter(
-- iterate through a set of issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- apply filter criteria to each issue
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember ) AND
DateInPeriod(
[Measures].[Issue closed date],
[Time].CurrentHierarchyMember )
),
-- numeric expression - sum of relevant issues
[Measures].[Issues created]
))
Hi,
And also, I need help with how I can convert Issues created, issues Open, Issues closed values to Percentage in Bar representation.? please help me on that as well.
Hello!
Could you please post formula for calculations [Issue Open] and [Closed Issues]?
Value of All Times depends on the formula that is used (it could be that one issue is counted in several years, but in All Times it will be counted only once).
Sum(
Filter(
–create a set of issues from selected projects only
Generate(
–create set of projects selected in pages
DescendantsSet(
[Project].CurrentHierarchyMember,
[Project].[Project]),
–creae set of issues in selected projects
DescendantsSet(
–retrieve project level from issue hierarchy bsed on selected project
[Issue].[Project].GetMemberByKey(
[Project].CurrentHierarchyMember.Key),
–level of memberrs for the set
[Issue].[Issue])
),
–primary filter - issue was creared in the selected period
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember)
),
–numeric value for sum - the issue was closed withn the selected period
[Measures].[Issues closed]
)
Formula for Issue Open:
[Measures].[Issues created] - [Measures].[Closed Issues]
This expression looks for the issues that are created and closed within the same Time dimension member. The Time dimension member “All Times” is a valid member, and the calculation is done with that member instead of summing up issues created and closed within the same month or year.
Meaning, you currently have already closed 17264 issues and only have 1326 open, although only 15298 were closed in the creation year.
You might hide the “All Times” by selecting the “Year” level from the Time dimension “All hierarchy level members” section.
