Need help with Structure formula.

[Sal]

Hello Community,

I am new to structure and need you help with a formula.

We have a demand to display number of completed stories before due date in a quarter by total number of stories due in that quarter under an epic.

Something like 23/40 - where 23 are completed before due date and 40 are the total number of stories due in that quarter.

Any help would be appreciated.

Best Regards,

Salvinder

[Valerie Knapp · Accepted solution]

Hi @Sal , thanks for your question.

Have you tried anything yet that you can share so we can help you?

The Structure documentation has some good examples to help you get started if you haven't already looked here - Creating a Formula (almworks.com)

Please share your work so far and we can try to assist you to get it up and running correctly.

Cheers

[Sal]

Thank you for the documentation@Valerie Knapp.

Here is what I have:

IF issuetype = "Epic":
SUM#children{IF issuetype = "story" and status = "done" and duedate < 01/04/2023:
1
}

The above I am using to show number of story completed whose due date on or before 31/Apr/2023.

IF issuetype = "Epic":
 SUM#children{IF issuetype = "story" and due_date < 01/04/2023:
 1
 }

This is used to show total number of story under epic whose due date on or before 31/Apr/2023.

when adding due date to the formula it doesn't show any data. 

changing the due date from duedate < 01/04/2023 to duedate > 01/04/2023 shows under epic one. don't know how due date works with formulas and not sure how I can display the results 23/40 format or something like that.

[Valerie Knapp]

Hi @Sal , thanks for this.

I have understood you are saying that the formula seems to ignore the due date as it is just counting the first two critera of issue type and status, so, in your case, it counts two Stories in the Status but then disregards the date.

Typically for this type of coding, it is very important the formatting of the text, in this example they provide, you can see they are referencing the due date but you have to type it exactly as dueDate. Have you tried amending your script in this way?

Sample Formulas (almworks.com)

IF dueDate < NOW():
DAYS_BETWEEN(dueDate, NOW()) CONCAT " days late"

Like you say, if you can get the date part working, the second part of the formula should be less complicated to write as it is checking for fewer criteria, just issues in the Epic.

If you still aren't getting anywhere, please open a ticket with Structure directly. I have found they are responsive and helpful when I had troubles with formulas.

Send Feedback (almworks.com)

[Sal · Accepted solution]

Hi @Valerie Knapp

Thank you for your help!

I had discussion with ALM Works and they helped me with the code and after some little modifications I was able to get desired results.

If issuetype = "Epic":
(with x= IF issuetype = "Epic":
SUM#children{IF issuetype = "user story" and status = "done" and duedate>=date("04/01/2023") and duedate<=date("06/30/2023"):
1
}:
with y= IF issuetype = "Epic":
SUM#children{IF issuetype = "user story" and duedate>=date("04/01/2023") and duedate<=date("06/30/2023"):
1
}:
with z=concat(x, "/", y):
if not defined(x) and defined(y): concat("0"; "/"; y)
else if defined(x) and not defined(y): concat(x; "/"; "0")
else if not defined(x) or not defined(y): concat("0"; "/"; "0")
else z
)