Count issues in specific status at the end of each month (historically)

Questions & Answers
, ,
1

I’m currently building a dashboard which includes two reports that have been causing me a lot of trouble. The first report: A table with “Open Issues” in columns and “Status” in rows. I have two calculated measures in “Status” that are grouping multiple status into a big one. They are both calculated like this:

Aggregate({
[Status].[1],
[Status].[2],
[Status].[3],
[Status].[4],
[Status].[5],
[Status].[6]
})

This report is supposed to show me the current number of issues in these statuses as of last import. If I reproduce this with a JQL-query I get the same amount of issues, so I’m assuming this is correct.

The second report: This one makes me lose sleep. It’s supposed to show the last 6 months and how many issues where in the status groups i defined.

This does not work. I use “Time” in rows and my own calculated measures, which are supposed to mimick the groups I created in the “Status” dimension.

If i use something like this the numbers of issues calculated are too high.

Sum(
{([Measures].[Issues history],[Transition Status].[1]),
([Measures].[Issues history],[Transition Status].[2]),
([Measures].[Issues history],[Transition Status].[3]),
([Measures].[Issues history],[Transition Status].[4]),
([Measures].[Issues history],[Transition Status].[5]),
([Measures].[Issues history],[Transition Status].[6])
},
[Measures].[Issues history]
)

When I use [Measures].[issue history],[Status].[1] instead the report retroactively changes if an issue gets resolved.

Is there no way to simply say “How many issues where in status group x at the end of the month” without the report changing retroactively and without the report counting issues multiple times if they jumped between statuses/transitioned between statuses?
I feel like the solution is simple but i just don’t see it.

2

Could it be that this current example of Cumulative Flow Diagram report is what you are looking for? But in table format
And you can create your own groups to have them selected and drilling into on the group you want …

1 Like
3

Hi, thank you so much for help - that looks pretty much exactly what I’m looking for.

I actually tried replicating this diagram a couple of times over the past few days and it seems my issue is of a different kind - my issues history paired with Transition Status show the wrong numbers for some reason, so I’ll probably have to dig deeper to find the source for that first before I can build the report

4

Hi @Kal,

You are very close with the second report.

The dimension Status relates to the current issue status. Therefore, the historical reports change when those old issues change their current status.
You should use the “Transition status” when referring to the historical values.
You could define the measure once in the expression.

Sum(
{[Transition Status].[1],
 [Transition Status].[2],
 [Transition Status].[3],
 [Transition Status].[4],
 [Transition Status].[5],
 [Transition Status].[6]},
[Measures].[Issues history])

You might as well drill through the issues to see if the individual issues also get values exceeding 1.
Please see more on drilling through issues here https://docs.eazybi.com/eazybi/analyze-and-visualize/interact-with-report#Interactwithreport-Interactwiththecellvalue.
If there are none exceeding 1, the total numbers might be justified.

If there are multiple showing more than 1, then the problem cause might come from another multi-value dimension.

In that case, share what other dimensions you use in the report.

Regards,
Oskars / support@eazyBI.com

1 Like
5

Hello Oskars,

thank you for your answer.

It took a bit of playing around but I found issues that had negative values and some had values exceeding 1. It seems that all of them are some of our earliest issues (over 5 years old) and thus something might not be quite right with them, our configuration has changed a lot since then. I have opted to exclude them from my report and finally the historical numbers look much better :slight_smile:

1 Like