List "Active Tickets" in easyBI

[YuX]

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

[martins.vanags]

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

[YuX]

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

[martins.vanags]

@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

[YuX]

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

[martins.vanags]

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

[YuX]

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

[martins.vanags]

Hi,
Try combining both codes into one calculated measure:

CASE WHEN (
[Measures].[Issues history],
[Transition Status].[Open Issues(New&Analysis)]
) > 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 history],
[Transition Status].[Open Issues(New&Analysis)]
) > 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

Martins / eazyBI

[YuX]

Thank you for your help, Martins. Your solution is really great and solves the problems perfectly.

[steve_bolan]

Hi Martins

Solution helped for one the teams for ages tracking work thank you.

I have a team using kanban and would like to understand and track the weekly average aged of work items from a certain points in the workflow

The full workflow is
Open
In analysis
Analysis Done
Selected for Development
**In Development **
Ready for Testing
In Testing
Ready for Release
In Release
Deployed

But I only wish to track aged in the 3 statuses in bold the development part.

So the work items that transition to In development and age is the recorded through in development, ready to test and in testing (I have a a transition status created for this - WIP Work Items)

The age tracking would end then the work items translations from In Testing into Ready for release.

I have tried to amend your calculated measure using the transition status WIP Work Items but as it has the created date in the measure - .get(‘created at’) it is counting from the creation date as expected and I don’t know how to write the calculated measure to record from the transition to In Development.

Any help would be greatly appreciated as I have been trying define a measure with no progress at all.

King regards
Steve

[martins.vanags]

@steve_bolan

In your case, perhaps another community post can be useful

How to make Report using Time in Status (Lead Time & Cycle Time Distribution)

Hello guys, I’m a newbie and unfortunately, I got a requirement from user to make these kinds of report : [Lead Time Distribution] Lead Time Distribution : X axis: days elapsed from open to closed Y axis: No of ticket Filter all closed “development” issues (closed in the past 90 days) and Cycle Time Distribution X axis: days elapsed from in Progress to closed Y axis: No of ticket Filter all closed “development” issues (closed in the past 90 days) I’ve tried to make the reports, but …

Martins / eazyBI

[Avishek_Man_Pradhan]

could you please look into this

Creating custom measure using mdx for open issues

Hi I wanted to use The “Open issues” measure but it calculates issues created - issues resolved however I wanted to custom list all the open issues which would include all except “done” and “won’t fix” status of the ticket. Please check the attachment for all the statuses available [Screen Shot 2022-08-15 at 11.59.07 PM]

[martins.vanags]

@Avishek_Man_Pradhan
This is a completely different question (not related to the topic above) and we have identified already.
You will have an answer from our team quite soon.

Martins / eazyBI