Structure Formula to filter based on field-value and show Σ remainingEstimate

[Igor]

Our environment:
Jira v8.18.0
Structure for Jira by ALM Worksv7.2.0

Current state:
I have created a structure to show/monitor effort on sub-tasks. I created 2 columns with "Σ Original Estimate" and "Σ Remaining Estimate". When looking at the sum of the values in those columns, A story will show the sum of all it's sub-tasks.

My Objective:
Add additional columns based on the 'Responsibility' of the sub-tasks. We have added a field to all sub-tasks called 'Responsibility' where we can expect one of 2 values: 'developer' or 'tester'.

I need to configure a 3rd column which will show ONLY the effort where 'responsibility' = 'tester'.

What I tried:
I can filter with jql like: type in (Sub-task) and responsibility = "tester"
This results in "0" or "1" based on the result of the query.

I need all occurences of "1" to show the result of the SUM of "remainingEstimate"

I assume I need a formula to do this?

Formula:
jql { type in (Sub-task) AND responsibility = "Tester" }

--> This results in 0/1 each line

Formula:
SUM{ remainingEstimate }

--> This results in the desired totals

Now I need to 'nest' these two formulas into one single formula that produces only totals for the filtered output

[Dionathan Lopes {ALM Works} · Accepted solution]

Hello @Igor 

Dionathan with ALM Works here.

You could try the following formula and let me know if it does help:

IF(issueType="Story";
SUM#CHILDREN{
if(type = "Sub-task" and responsibility = "tester"; RemainingEstimate)})

You may need to change the format to Duration and check the Work time option, as below:

Basically, it will sum only the Remaining estimate values of the sub-tasks with responsibility = Tester, showing the total on its Parent Story.

PS: If the Story has its own Remaining estimate value and you need to aggregate this value, just remove the #CHILDREN from the SUM function.

Looking forward to hearing from you.

Best Regards,
Dionathan

[Igor]

Hello @Dionathan Lopes {ALM Works}, much appreciated!

I've tried your suggestion immediately and confirm this is the solution for my needs, thank you very much! I will tweak and tune as I go, but I can work with this perfectly!

Cheers,
Igor