eazyBI report - how to create a query of the verified issued older than 7 days

[Yakir Giladi]

I have a report of the last month by day (see attachment).

I want to display the issues were open (status != verified) on that date.

The 'resolved' status is not 'closed' in our company, it's the 'verified' status - filter status!=verified

need issue condition: issue age > 7 days, relatively to the dates in the table

the age interval displaying me 7 days from today, I need it relatively to the dates in the table

Table Definition:

{
"cube_name": "RnD",
"cube_reports": [ {
"name": "Metrics Teams - Open Bugs Trend - source",
"folder_name": "Metrics Teams",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[open issues not verified]","[Measures].[created by date count - yakir]"],"members":[]}]},"rows":{"dimensions":[{"name":"Project","selected_set":["[Project].[RnD]"],"members":[],"bookmarked_members":[]},{"name":"Issue Type","selected_set":["[Issue Type].[Sub-Bug]"],"members":[],"bookmarked_members":[]},{"name":"Time","selected_set":["[Time.Weekly].[Day].Members"],"members":[],"bookmarked_members":[{"depth":2,"name":"Q2 2017","full_name":"[Time].[2017].[Q2 2017]","drillable":true}]}],"filter_by":{"conditions":[{"expression":"[Time.Weekly].CurrentHierarchyMember","operator":"between","value":"1 months ago and today","value_type":"date"}]},"nonempty_crossjoin":false},"pages":{"dimensions":[{"name":"Teams","selected_set":["[Teams].[Devices1]","[Teams].[All Teams]"],"members":[{"depth":0,"name":"All Teams","full_name":"[Teams].[All Teams]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Teams].[All Teams]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"freeze_header":true}}}
} ],
"calculated_members": [{"name":"open issues not verified","dimension":"Measures","formula":"CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues closed])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues closed]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n -- optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get('Created at'),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get('Closed at'),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND","format_string":""},{"name":"created by date count - yakir","dimension":"Measures","formula":"iif(isEmpty([Measures].[Issues created count]),\n 0,\n [Measures].[Issues created count])","format_string":""}]
}

Thanks,

Yakir Giladi,

Tufin