How to count the time in a specific state month by month?

[Guilherme_Zambonato]

I need to calculate how long the issue has been going on month by month. However, I can’t get the issue line to repeat from month to month.

[Elita.Kalane]

Hello @Guilherme_Zambonato

Thanks for posting your question!

The solution you are looking for can be achieved by using JavaScript code. Please follow this Community post to import a new field, “Workdays in Status by months”

Time that tickets stayed in a specific status during a specific period

Hi, The default measure of Days in transition status does not split the days by the period selected in the report. Instead, it shows the full number of days spent in the status for the period when the issue transitioned from the status. It is possible to create a workaround with a Javascript calculated custom field. I used the following advanced settings for the solution: [jira.customfield_wd_inst_by_month] name="Workdays in status by months" data_type="decimal" measure=true multiple_dimensi…

When you update your eazyBI advanced settings with the code in the Community post I shared, please select a new field, “Workdays in status by months,” for import and use it in your report similarly to the screenshot below. Note that the code will only work at the month level.

Best wishes,

Elita from support@eazybi.com

[Guilherme_Zambonato]

Hello @Elita.Kalane
The script worked well, however it not completely solve my problem =(

Imagine the following Example:
Example: ISSUE A is at “Ready” transition status on “2024/01/16” and continues to date now without changing status.

The non-accounting solution covers the period in which it was ready in the months of January, February, March and April until the current moment.

In other words, the count only happens when the problem moves to another state. Is there any way to count if the status is still true without transition?

[Elita.Kalane]

Hi @Guilherme_Zambonato
For those cases where the status hasn’t changed, can you please try if the formula below returns the expected results when you define it under Measures dimension?

CASE
WHEN
DateInPeriod(
TimestampToDate(([Measures].[Transition to first timestamp],
[Transition Field].[Status])),
[Time].CurrentHierarchyMember)
THEN
DateDiffWorkDays(
  TimestampToDate(([Measures].[Transition to first timestamp],
[Transition Field].[Status])),
[Time].CurrentHierarchyMember.NextStartDate
)
WHEN
DateInPeriod(
TimestampToDate(([Measures].[Transition from first timestamp],
[Transition Field].[Status])),
[Time].CurrentHierarchyMember)
THEN
DateDiffWorkDays(
[Time].CurrentHierarchyMember.StartDate,
  TimestampToDate(([Measures].[Transition from first timestamp],
[Transition Field].[Status]))
)
WHEN
IsEmpty([Measures].[Transitions to status]) AND
IsEmpty([Measures].[Transitions from status]) AND
[Measures].[Issues history] >0
THEN
DateDiffWorkDays(
  [Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchyMember.NextStartDate
)

END

Best wishes,
Elita from support@eazybi.com