Structure Question - Finding story points for children of a certain issueType and certain status

[mattics phi]

Hi!  Quick question!  What I am trying to do is make a formula where I count all the children that are missing story points writing that total count in a sentence.  I then display the total count at the Epic level.

"xx out of zz" where xx is the children that are missing story points and zz is the total children.  Currently, what I have below in the code block is working but it counts children of ALL issuetypes and ALL statuses.

My question is this: how can I filter out for status = open and issueType = story? 

If there is a better way of writing this out, please let me know!  This is my first time using structures

if issuetype = "Epic":
    if (COUNT#children{"Story Points"} - COUNT#children{columnSP} == 0):
        "All stories are pointed"
    else : 
        concat(COUNT#children{"Story Points"} - COUNT#children{columnSP}; " out of "; COUNT#children{"Story Points"})

columnSP = column that displays the number of stories that have story points

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @mattics phi 

You can try a formula like this:

if issuetype = "epic":

if sum#children{if issuetype = "story" and status = "open" and storypoints: "1"} = sum#children{if issuetype = "story" and status = "open": "1"} : "All stories are pointed" else
concat(sum#children{if issuetype = "story" and status = "open": "1"} - sum#children{if issuetype = "story" and status = "open" and storypoints: "1"}, " out of ", sum#children{if issuetype = "story" and status = "open": "1"})

It will do the following for every Epic:

- count how many Stories in the 'Open' Status each Epic has;
- count how many of such Stories have Story Points;
- return either 'All stories are pointed' if both parameters match or 'X out of Y' when only X Stories have Story Points out of Y Stories overall.

I hope this helps. If you need further assistance, please reach out to us directly at our support portal.

Best regards,
Stepan Kholodov
Tempo

[mattics phi]

Thank you!  Sorry, I was on PTO.  It looks complex but it works, thank you so much!

Because of the complexity, I made some support columns to help me calculate the same: filterForStatus and filterByIssueKey.  I would use these columns in the calculation that I posted in the OP!

filterForStatus

if JQL {issueType = Epic} :
  count#strict{
if JQL {status in ("Cancelled", Closed)}: 1 }

filterByIssueKey

if JQL {issueType = Epic} :
  count#strict{
if JQL {issueType in ("Task", "Sub-task")}: 1 }

[Kyle Silverstein]

The math is slightly misleading in this formula. I think it would be more clear if you are displaying the ratio of unpointed tickets to total tickets. 

In the current formula, lets say you have 9 open tickets and 3 of them have story points, this formula will print out

(9-3) of 9.   

or

6 of 9

Which is saying that you have 6 of the total 9 which have yet to be pointed. If you point another child, the print out will be  "5 of 9". which feels counter intuitive. 

In this version, I reverse the logic so it counts up vs down. I also removed the conditions indicating if the tickets are open/closed, because in my use case I didn't want that, however this would be easy to add back in. I also added some color formatting 

if issuetype = "epic":

if sum#children{"1"} :

if COUNT#children{storypoints} = sum#children{"1"} : ":panel[ **Fully Pointed** ]{backgroundColor=#4CAF50 color=white}" else

concat(sum#children{"1"} - (sum#children{"1"} - COUNT#children{storypoints}), " of ", sum#children{"1"}, " Pointed")

else ":panel[ **No Children** ]{backgroundColor=#F5BE41 color=black}"