Cum Children in parent

Questions & Answers
,
1

Hi,
I don’t understand how i must do, can you help me ?
I have Measure (User defined) : Niveau Criticité Faible for Task Issue
I have a good result
I would like to calculate number of issues that have this result

For exemple see attachment
On level BNQ-1339, i would like the sum and count of its children for Measures “Niveau Criticité Faible” (normally result = 2) of BNQ-1355 and BNQ-1353
image
and you can find the Report Export below
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Test risque”,
“folder_name”: “ZZ_Tests Jerome”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]","[Measures].[Niveau Criticité Faible]","[Measures].[Niveau Criticité Moyenne]","[Measures].[Niveau Criticité élevé]","[Measures].[Cumulative issues resolved]","[Measures].[Compter enfants]","[Measures].[Issue Criticité]","[Measures].[Test calcul children]"],“members”:[{“depth”:0,“full_name”:"[Measures].[Test calcul children]",“format_string”:"#,##0.00",“report_specific”:true,“name”:“Test calcul children”,“calculated”:true}]}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:["[Issue.Epic].[SW - BANQUE].[BNQ-1339]"],“members”:[{“depth”:2,“name”:“BNQ-1339”,“full_name”:"[Issue.Epic].[SW - BANQUE].[BNQ-1339]",“drillable”:true,“key”:“BNQ-1339”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[]}],“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”}],“bookmarked_members”:[],“current_page_members”:["[Project].[All Projects]"]},{“name”:“Issue”,“duplicate”:true,“selected_set”:["[Issue.Epic].[All Issues by epics]"],“members”:[{“depth”:0,“name”:“All Issues by epics”,“full_name”:"[Issue.Epic].[All Issues by epics]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“SW - BANQUE”,“full_name”:"[Issue.Epic].[SW - BANQUE]",“drillable”:true,“key”:“BNQ”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Issue.Epic].[All Issues by epics]"},{“depth”:2,“name”:“BNQ-1339”,“full_name”:"[Issue.Epic].[SW - BANQUE].[BNQ-1339]",“drillable”:true,“key”:“BNQ-1339”,“parent_full_name”:"[Issue.Epic].[SW - BANQUE]"}],“bookmarked_members”:[],“current_page_members”:["[Issue.Epic].[SW - BANQUE].[BNQ-1339]"]}]},“options”:{“total”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Test calcul children”,“formula”:“NonZero(\nCount(\nFilter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), [Measures].[Percentage complete]\u003c100)\n)\n)”,“format_string”:""}]}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue created date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Created at’)"},{“name”:“Issue Criticité”,“dimension”:“Measures”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Criticité’)",“format_string”:"#,##0.00"},{“name”:“Niveau Criticité Faible”,“dimension”:“Measures”,“formula”:“case\nWHEN\n [Measures].[Issue Criticité]\u003c=3\nthen \n [Measures].[Issues created] \nEND\n”,“format_string”:"#,##0"},{“name”:“Niveau Criticité Moyenne”,“dimension”:“Measures”,“formula”:“CASE\n\nWHEN\n [Measures].[Issue Criticité] \u003e=3 and [Measures].[Issue Criticité] \n\u003c9\nthen \n [Measures].[Issues created]\n \nEND\n”,“format_string”:"#,##0"},{“name”:“Niveau Criticité élevé”,“dimension”:“Measures”,“formula”:“CASE\n\nWHEN\n [Measures].[Issue Criticité]\u003e9\n \n Then \n [Measures].[Issues created]\n \n END”,“format_string”:"#,##0"},{“name”:“Cumulative Issues resolved”,“dimension”:“Measures”,“formula”:“Cache(\n Sum({PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember},\n [Measures].[Issues resolved]\n )\n)\n”,“format_string”:""},{“name”:“Cumulative issues resolved”,“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[Issues resolved] \u003e 0 THEN\n Sum({PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember},\n [Measures].[Issues resolved]\n )\nEND”,“format_string”:""},{“name”:“Compter enfants”,“dimension”:“Measures”,“formula”:“CASE\nWhen \n[Measures].[Issue Criticité] \u003e0 \nTHEN\nSum ({PreviousPeriods([Time].CurrentHierarchyMember),\n [Time].CurrentHierarchyMember}, \n (case \n WHEN\n [Measures].[Issue Criticité]\u003c=3\n then \n Count([Measures].[Issue created date])\nEND))\n \n END\n”,“format_string”:""}]
}

Thanks for your help , because i saw more topics and i can’t find my solution

Best regards

Jérôme

2

Hi @Jerome_Arpin ,

It seems to me that Criticité is a numeric custom field. In such a case, you could use the hidden measure that gives total of issues with this value: Issues with Criticité created, see Custom Fields - eazyBI for Jira.

Please try to change the formula of Niveau Criticité Faible with only this measure and see if that helps.

[Measures].[Issues with Criticité created]

Lauma / support@eazybi.com

3

Hello

I tried it, and no result like i want
I would like to have number of issue in Epic with criticité Faible
I use custom field Criticité, the field is numeric

To calculate criticité faible , see Measure :
CASE
WHEN
[Measures].[Issue Criticité] >=3 and [Measures].[Issue Criticité] <9
then
[Measures].[Issues with Criticité created]
END

Thanks by advance for your help

4

Hi @Jerome_Arpin,

Sorry, I misunderstood the requirement!
You can use the Sum() function to add the values to higher level

Sum(
Filter(Descendants(
  [Issue.Epic].CurrentHierarchyMember, [Issue.Epic].[Parent]),
  [Measures].[Issue Criticité] >= 3 AND 
  [Measures].[Issue Criticité] < 9),
[Measures].[Issues with Criticité created]  
)

But if you need to use the Criticité ranges often, I suggest you consider creating custom interval dimension that you could use with any other measure: Advanced settings for custom fields - eazyBI for Jira.

Lauma / support@eazybi.com

5

Hello, thanks for your help

Kind regards

1 Like