How to count issues by time since last update when Rows is not Issue

Questions & Answers
, ,
1

I am currently creating a table in eazyBI.

My goal is to create measures that group issues by how much time has passed since their last update, so that I can follow up on issues that have not been updated for a while.
Specifically, I would like to count issues by attributes (such as team or assignee) for the following ranges:

  • Issues last updated 15–29 days ago

  • Issues last updated 30–59 days ago

I am planning to place Team or Assignee on Rows.

I was able to create a measure that works when Issue is placed on Rows.
However, when I place other dimensions (such as Team or Assignee) on Rows, the aggregation does not work as expected.

I would like to know how to create a measure that can be aggregated correctly even when the Rows dimension is not Issue.

2

Hi @Kawanaka,

Thanks for posting your question!

To count issues by time since last update when using Team or Assignee in Rows, you have a few approaches:

1) Use a predefined measure with the Time dimension.

Use the predefined measure “Issues last updated” combined with the Time dimension. This measure automatically aggregates correctly across any dimension (Team, Assignee, etc.).

  • Place Team or Assignee in Rows

  • Create new calculated members in the Time dimension for 15-29 days and 30-59 days. Select them in the Report Rows.

  • The “Issues last updated” measure will count issues by their last update date

1702×710 54.5 KB

2) Create calculated measures with proper aggregation.

For more specific ranges, create calculated measures like this:
Issues updated 15-29 days ago:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateDiffDays([Measures].[Issue updated date], Now()) >= 15
    AND
    DateDiffDays([Measures].[Issue updated date], Now()) <= 29
  ),
  CASE WHEN [Measures].[Issues created] > 0 THEN 1 END
)

Issues updated 30-59 days ago:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateDiffDays([Measures].[Issue updated date], Now()) >= 30
    AND
    DateDiffDays([Measures].[Issue updated date], Now()) <= 59
  ),
  CASE WHEN [Measures].[Issues created] > 0 THEN 1 END
)

Here’s an example of this report from my instance:

1590×844 76.6 KB

3) Create “Age since updated interval” dimension.

Create a JavaScript calculated field that groups issues into intervals (15-29 days, 30-59 days, etc.). Then use this dimension in Rows/Pages with “Issues created” measure.

This requires adding a custom field in Source Data → Import options with JavaScript code and interval settings.

See Age since updated interval documentation

Best,
Marita from eazybi@support.com