Number of issues left in current status over X days

Dear There,

I got this problem… might be somewhat similar to previous question not answered yet, but maybe a bit more easier

Basically, need to create calculation, to get number of issues left in existing status (e.g. status “OPEN”) more than 10 days etc.

I tried to use below calculation from examples but could not define status and the date latest status update seems unknown… I also tried to use default [Measure].[Issue status updated date] and it is not working.

Please kindly advise… Thanks!

Leon Li


Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateDiffDays([Issue].CurrentMember.get(‘Updated at’),
Now()) >= 10

The report export in below

“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “ThinOS SUS metric #11 Days left in OPEN CLARIFY”,
“folder_name”: “Leon Kan Li (please do not edit)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]","[Measures].[10 days from last update]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Status”,“selected_set”:["[Status].[All Statuses]","[Status].[> Not submitted to Engineering yet]","[Status].[>>> IPS clarify/feedback]","[Status].[>>> SQA anlysis confirm]","[Status].[>>> DEV WIP]"],“members”:[],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“ThinOS Defect Management”,“full_name”:"[Project].[ThinOS Defect Management]",“drillable”:true,“key”:“THINOS”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[ThinOS Defect Management]"]},{“name”:“Found By”,“selected_set”:["[Found By].[All Found Bies]"],“members”:[{“depth”:0,“name”:“All Found Bies”,“full_name”:"[Found By].[All Found Bies]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Customer”,“full_name”:"[Found By].[Customer]",“parent_full_name”:"[Found By].[All Found Bies]"}],“bookmarked_members”:[],“current_page_members”:["[Found By].[Customer]"]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:false}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue updated date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Updated at’)",“annotations”:{“group”:“Issue properties”}},{“dimension”:“Measures”,“name”:“Issue status updated date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Status updated at’)",“annotations”:{“group”:“Issue properties”}},{“name”:"> Not submitted to Engineering yet",“dimension”:“Status”,“formula”:"[Status].[Open]",“format_string”:""},{“name”:">>> SQA anlysis confirm",“dimension”:“Status”,“formula”:“Aggregate({\n\n[Status].[Submitted] \n,\n[Status].[SQA Confirm]\n})”,“format_string”:""},{“name”:">>> DEV WIP",“dimension”:“Status”,“formula”:“Aggregate({[Status].[In Engineering], [Status].[Assigned/Analyze]})”,“format_string”:""},{“name”:">>> IPS clarify/feedback",“dimension”:“Status”,“formula”:“Aggregate({[Status].[Clarify] \n\n–, \n\n–[Status].[SQA Confirm]\n\n})”,“format_string”:""},{“name”:“10 days from last update”,“dimension”:“Measures”,“formula”:"–Count(\n-- Filter(\n–\t Descendants([Issue].CurrentMember, [Issue].[Issue]),\n-- DateDiffDays([Measures].[Issue status updated date],\n-- Now()) \n-- >= 10\n–))\nNonZero(\n Count(\n Filter(\n\t Descendants([Issue].CurrentMember, [Status].CurrentMember),\n–\t DateDiffDays([Measures].[Issue updated date],\n-- Now()) >= 10\n \n-- AND \n DateDiffDays([Issue].CurrentMember.get(‘Created at’),\n Now()) >=10\n)))\n",“format_string”:""}]

There is a report example for how long issues are in particular status in our demo account:

There is some problem with the report definition. However, I noticed you are using a bit different formula there then the one represented separately.

You are very close to a solution with the formula you shared. It misses a filter by measure there.

          Descendants([Issue].CurrentMember, [Issue].[Issue]),
             [Issue].CurrentMember.get("Updated at"),
             Now()) >= 10
         [Measures].[Issues created] > 0 -- this line was missing there

You can use the same calculation with Status updated at property as well.

Daina /

I am looking for something similar, however, I want to be able to select X days old in a filter, and have the user be able to query tickets that have not been updated in X days (based on filter value) is this possible?

You would like to use some dimension for a period selection here. eazyBI has two default interval dimensions:
Age interval representing a time how old an issue is since the creation date till now (for unresolved issues only)
Resolution interval representing a time how long it took to resolved the issue (for resolved issues only).

We have an example in our documentation on how to add a new Interval dimension Age since updated interval with JavaScript calculated custom fields.

You can define intervals you would like to use a selection.

Daina /