Time calculated member

Questions & Answers
1

Hi, I need to define a new calculate member in TIME that allow me filter by year, q, month, week. or day, by the field “Start Time”.
image

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Prueba releases",
     "folder_name": "test",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Releases]"],"members":[]},{"name":"Status","selected_set":["[Status].[Rollbacked]","[Status].[Releases]"],"members":[],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Release Team","selected_set":["[Release Team].[All Release Teams]"],"members":[{"depth":0,"name":"All Release Teams","full_name":"[Release Team].[All Release Teams]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Releases]","[Status].[Rollbacked]"],"order":"DESC"},"nonempty_crossjoin":true},"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":"Release Management","full_name":"[Project].[Release Management]","drillable":true,"key":"REL","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[Release Management]"]},{"name":"Time","selected_set":["[Time].[All Times]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time].[All Times]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":true},{"depth":1,"name":"2023","full_name":"[Time].[2023]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[All Times]"},{"depth":2,"name":"Q2 2023","full_name":"[Time].[2023].[Q2 2023]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[2023]"},{"depth":2,"name":"Q3 2023","full_name":"[Time].[2023].[Q3 2023]","drillable":true,"expanded":true,"drilled_into":false,"parent_full_name":"[Time].[2023]"},{"depth":3,"name":"Aug 2023","full_name":"[Time].[2023].[Q3 2023].[Aug 2023]","drillable":true,"parent_full_name":"[Time].[2023].[Q3 2023]"}],"bookmarked_members":[],"current_page_members":["[Time].[2023].[Q3 2023].[Aug 2023]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Releases","full_name":"[Issue Type].[Releases]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Major Release","full_name":"[Issue Type].[Major Release]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":[],"current_page_members":["[Issue Type].[Releases]","[Issue Type].[Major Release]"]},{"name":"Status","duplicate":true,"selected_set":["[Status].[Rollbacked]","[Status].[Releases]"],"members":[{"depth":0,"name":"Rollbacked","full_name":"[Status].[Rollbacked]","calculated":true,"dimension":"Status"},{"depth":0,"name":"Releases","full_name":"[Status].[Releases]","calculated":true,"dimension":"Status"}],"bookmarked_members":[],"current_page_members":["[Status].[Rollbacked]","[Status].[Releases]"]}]},"options":{"nonempty":true,"total":true},"view":{"current":"table","maximized":false,"table":{"freeze_header":false}},"calculated_members":[]}
  } ],
  "calculated_members": [{"name":"Releases","dimension":"Status","formula":"[Status].[Done] - [Status].[Rollbacked]","format_string":""},{"name":"Releases","dimension":"Measures","formula":"[Measures].[Issues created],[Status].[CLOSE, DONE]-[Transition Status].[Rollback]","format_string":""},{"name":"Rollbacked","dimension":"Status","formula":"([Measures].[Transitions from status issues count],\n[Transition Status].[Rollback]) ","format_string":""}]
}
2

Hey @gustavo_salvato

There can be only one date/date-time dimension in the eazyBI “Issues” cube, and there is already a “Time” dimension with that data type out-of-box.
Instead of importing your date picker Jira field as a separate dimension, it should be enough to import it as a measure and property , and then use the numerical measure "Issues with " with the “Time” dimension members.

When using the “Time” dimension with that numerical measure, eazyBI would count the issues where your date picker field value belongs to the selected/displayed period.

See also this video on how to use the “Time” dimension with different numerical measures in eazyBI:
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports/time-dimension

Martins / eazyBI

3

Thanks for your answer @martins.vanags .
But how can with this measure Issue with, I don´t know how to include here only the issues with [Status].[Done]

([Measures].[Issues with start time]-
[Measures].[Rollbacked] - [Measures].[Resolution_Cancelled])

4

@gustavo_salvato

You can try using tuples
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/tuple

Try this formula:

([Measures].[Issues with start time],
[Status].[Done])
-
([Measures].[Rollbacked],
[Status].[Done]) 
- 
([Measures].[Resolution_Cancelled],
[Status].[Done])

Martins / eazyBI support

5

Thanks, but I found a problem, I need to sum the followings measures
([Measures].[Issues with start time],[status].[Done],[Resolution].[Done])
([Measures].[Issues with start time],[status].[Done],[Resolution].[Resolved])
but discharging those issues that were through the status Rollback ([Measures].[Transitions from status issues count],[Transition Status].[Rollback])

regards

6

Hi @gustavo_salvato

Try this formula:

Sum(
	{
		[Resolution].[Done],
		[Resolution].[Resolved]
	},
	(
		[Measures].[Issues with start time],
		[Status].[Done]
	)
)
-
(
	[Measures].[Transitions from status issues count],
	[Transition Status].[Rollback]
)

Martins / eazyBI

1 Like
7

Amazing .

Thank you!