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!
Regards,
Leon Li
NonZero(
Count(
Filter(
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”:""}]
}