List "Active Tickets" in easyBI

Hello Community,

I am trying to set up a report in easyBI to demonstrate the few KPIs below based on Sample Report “Created vs resolved issues over time”

Our JIRA projects have the status defined on each tickets

  • New
  • Under Analysis
  • Analysis Done
  • Integration
  • Closed

KPI1. Total amount of “Active Tickets” per weekly

"Active Tickets" = Tickets with status under New and Under Analysis.

The Sample Report “Created vs resolved issues over time” has the Predefined Measure “Open Issues” but it will list total amount of tickets which are under status New/Under Analysis/Analysis Done/Integration while I want to exclude the tickets of Analysis Done and Integration status.
(I tried to add dimension - STATUS to Page, this will allow me to filter multiple status (select New and Under Analysis) manually, however, it will impact other column in the table, e.g Issues Resolved number will be always 0 every week)

CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
Cache([Measures].[Issues created]
- [Measures].[Issues resolved])
))
+ [Measures].[Issues created]
- [Measures].[Issues resolved]
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
– optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END

Do you have idea how to get Total amount of “Active Tickets” (customized and specify the status of “New” and “Under Analysis”) per weekly?

KPI2. Average age of “Active Tickets” per weekly.

"Active Tickets" = Tickets with status under New and Under Analysis.

Similarly, this intends to get the average age of “Active Tickets” per weekly. if using the predefined measure below, it will get the average age for open issues (All status except for Closed )

CASE WHEN [Measures].[Open issues] > 0 THEN
Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filter open issues in period using issue properties Created date and Resolution date only
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember)
),
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
– cumulative age of each issue for any period till end of period or till today
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN DateDiffDays([Issue].CurrentMember.get(‘Created at’),
Now())
ELSE DateDiffDays([Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember.NextStartDate)
END
END
)
END

Do you have idea how to get the average age of “Active Tickets” per weekly (customized and specify the status of “New” and “Under Analysis”) per weekly?

Thanks

Hi,

Try using the sample report “Unresolved issues by statuses over time” from your account.
It is using the predefined measure “Issues history” with “Transition status” dimension members.
This measure would calculate the number of issues in a status (from “Transition status” dimension) at the end of the time period.
When used with “Time” dimension you can see a historical number of issues in the specific status.

See more about “Issues history” measure here:
https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/import-issue-change-history#Importissuechangehistory-Calculatedmeasures

Martins / eazyBI support

Thank you for your help, Martins. After adding measure “Issues history” with “Transition status” (add new member to list New&Analysis ticket only), I can correctly get the total number of “Active Tickets” per weekly.

However, “Transition Status” is also applied to other measures and this causes all measures blank accordingly.
I would like to keep Active Ticket per weekly as well as Issues Created Tickets per weekly in same table.

Is there a way to let dimension member - Transition Status applying to Issue History only without applying to other measure in the table?

Thanks

@YuX

In this case, create a new calculated measure using this formula with tuples:
It would take the one transition status dimension member in the calculation even if the “Transition Status” would not be used in the report.

(
[Measures].[Issues history],
[Transition Status].[Open Issues(New&Analysis)]
)

Next, select your new calculated measure instead of “Issues history”.

Then remove the “Transition Status” dimension from the report because it is already hardcoded in your calculated measure.
All other measures should work if “Transition Status” is removed from the report.
But the new calculated measure would use still the member from the “Transition Status” dimension.

Martins

Thanks for your help, Martins. It perfectly solves my questions for KPI1. Total amount of “Active Tickets” per weekly

Regarding the KPI2. Average age of “Active Tickets” per weekly., I would like to use predefined measure [Measures].[Average age of Open issues] below. However, this measure only lists the average date for Open Issues( ticket without Resolved at).

Is it possible to combine the new measure you mentioned above (listing ACTIVE tickets) with measure of Average age of Open issues? This will help to list the average days for those ACTIVE tickets.

CASE WHEN [Measures].[Open issues] > 0 THEN
  Avg(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter open issues in period using issue properties Created date and Resolution date only
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember)
      ),
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    THEN
    -- cumulative age of each issue for any period till end of period or till today
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
      THEN DateDiffDays([Issue].CurrentMember.get('Created at'),
        Now())
      ELSE DateDiffDays([Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember.NextStartDate)
      END
    END
  )
END

Thanks

Hi,

Your observation is not fully correct about the second calculated measure.
It suppose to calculate average age of unresolved tickets at each week, it won’t exclude all currently resolved issues.
If issue was open at weekX and currently is resolved, it would still be included in weekX calculation for average age.

Martins / eazyBI

Hello Martins,

Yes, you are right. If issue A was open at weekX and currently is resolved at WeekY afterwards, it would still be included in weekX calculation for average age → This is the exact model I need since WeekX calculation is not supposed to be affected even if Issue A is resolved at WeekY.

This is the reason why I am referring to measure - Average age of Open issues, it can meet the requirement except for this average date is about Unsolved tickets while I need the average date for ACTIVE tickets (listed in KPI1)

Do you have idea how to get the Average age of Active Tickets (Tickets belong to [Transition Status].[Open Issues(New&Analysis)]) ?

Thanks