# List "Active Tickets" in easyBI

**URL:** <https://community.eazybi.com/t/list-active-tickets-in-easybi/6504>\
**Category:** Questions & Answers\
**Created:** [April 2, 2021, 7:01am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504 "2021-04-02T07:01:43Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![YuX](https://community.eazybi.com/user_avatar/community.eazybi.com/yux/32/4590_2.png) [@YuX](https://community.eazybi.com/u/YuX)\
**Post date:** [April 2, 2021, 7:01am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/1 "2021-04-02T07:01:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [April 12, 2021, 8:06am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/2 "2021-04-12T08:06:22Z")

</div>

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](https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/import-issue-change-history#Importissuechangehistory-Calculatedmeasures)

Martins / eazyBI support

 ![report](https://community.eazybi.com/uploads/default/original/2X/3/3f9d562927a8af0aa73ad887dc64d97a80c283cb.png)

---

<div class="post-metadata">

**Author:** ![YuX](https://community.eazybi.com/user_avatar/community.eazybi.com/yux/32/4590_2.png) [@YuX](https://community.eazybi.com/u/YuX)\
**Post date:** [April 19, 2021, 8:07am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/3 "2021-04-19T08:07:21Z")

</div>

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.

 ![image](https://community.eazybi.com/uploads/default/original/2X/6/694cbcb8289351e4dfd5511213f4e05d5a92a541.png)

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [April 19, 2021, 8:46am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/4 "2021-04-19T08:46:49Z")

</div>

@YuX

In this case, create a new [calculated measure](https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/calculated-measures) 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

---

<div class="post-metadata">

**Author:** ![YuX](https://community.eazybi.com/user_avatar/community.eazybi.com/yux/32/4590_2.png) [@YuX](https://community.eazybi.com/u/YuX)\
**Post date:** [April 20, 2021, 3:31am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/5 "2021-04-20T03:31:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [April 20, 2021, 4:25am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/6 "2021-04-20T04:25:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![YuX](https://community.eazybi.com/user_avatar/community.eazybi.com/yux/32/4590_2.png) [@YuX](https://community.eazybi.com/u/YuX)\
**Post date:** [April 20, 2021, 7:09am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/7 "2021-04-20T07:09:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [April 23, 2021, 8:07am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/8 "2021-04-23T08:07:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![YuX](https://community.eazybi.com/user_avatar/community.eazybi.com/yux/32/4590_2.png) [@YuX](https://community.eazybi.com/u/YuX)\
**Post date:** [May 7, 2021, 1:21am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/9 "2021-05-07T01:21:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steve\_bolan](https://community.eazybi.com/user_avatar/community.eazybi.com/steve_bolan/32/7252_2.png) [@steve\_bolan](https://community.eazybi.com/u/steve_bolan)\
**Post date:** [June 6, 2022, 7:08pm UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/10 "2022-06-06T19:08:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [June 9, 2022, 2:55pm UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/11 "2022-06-09T14:55:00Z")

</div>

@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)](https://community.eazybi.com/t/how-to-make-report-using-time-in-status-lead-time-cycle-time-distribution/4204):
>
> Hello guys, I’m a newbie and unfortunately, I got a requirement from user to make these kinds of report : 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

---

<div class="post-metadata">

**Author:** ![Avishek\_Man\_Pradhan](https://community.eazybi.com/user_avatar/community.eazybi.com/avishek_man_pradhan/32/7799_2.png) [@Avishek\_Man\_Pradhan](https://community.eazybi.com/u/Avishek_Man_Pradhan)\
**Post date:** [August 15, 2022, 6:27pm UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/12 "2022-08-15T18:27:50Z")

</div>

could you please look into this

> [@Creating custom measure using mdx for open issues](https://community.eazybi.com/t/creating-custom-measure-using-mdx-for-open-issues/10336):
>
> 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

---

<div class="post-metadata">

**Author:** ![martins.vanags](https://community.eazybi.com/user_avatar/community.eazybi.com/martins.vanags/32/8905_2.png) [@martins.vanags](https://community.eazybi.com/u/martins.vanags)\
**Post date:** [August 16, 2022, 6:23am UTC](https://community.eazybi.com/t/list-active-tickets-in-easybi/6504/13 "2022-08-16T06:23:03Z")

</div>

@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