Structure for Jira _ creating a formula that will track the history of changes over a certain period

[Alexander Bondarev]

Hi Community! 

We are using app Structure - Flexible Jira Project Management (Data Center) from ALM Works.

I want to create a formula that will track the history of status changes over a certain period of time. 

My business case is to control returns from QA on certain period, for example, from custom dates (between 2022-07-25 and today).   

My current formula is:

IF JQL {type in standardIssueTypes()} AND history.changes.filter($.field = "status" and $.to = "READY FOR TEST").size() > 1:
history.changes.filter($.field = "status" and $.to = "READY FOR TEST").size() - 1

ELSE IF JQL {type in standardIssueTypes()} AND history.changes.filter($.field = "status" and $.to = "READY FOR TEST").size() <= 1:
history.changes.filter($.field = "status" and $.to = "READY FOR TEST").size()

Рow to make such logic?

[David Niro · Accepted solution]

Hello @Alexander Bondarev !

David from ALM Works here.

This is a great formula! 

If I understand correctly, it's counting the number of times that a status was changed to "READY FOR TEST", excluding the first time it was set to this status, unless the first time was the only time it was set.

Your goal is to do basically the same thing, but after a certain date?  If I have this correct, the formula below should accomplish your goal:

WITH In_Progress_Change =
history.changes.filter($.field = "status" and $.to = "In Progress" and $.changegroup.timestamp > date("2022-07-25"))
:

IF JQL {type in standardIssueTypes()} AND In_Progress_Change.size() > 1:
In_Progress_Change.size() - 1

ELSE IF JQL {type in standardIssueTypes()} AND In_Progress_Change.size() <= 1:
In_Progress_Change.size()

I did two things.

1. added the $.changegroup.timestamp > datetime() to the history filter you created to specify the date you want to start from
2. created a local variable for your history filter since it was used in more than one spot in your formula.  this will help with performance if the structure gets very large, or additional formulas are added to it

If I misunderstood, and your goal was to get rid of the "ELSE IF" part of the formula, you should be able to delete it, remove the "-1" from the first IF statement and modify the size requirement.  This will give you the number of changes since the date in the date() function. 

WITH In_Progress_Change =
history.changes.filter($.field = "status" and $.to = "In Progress" and $.changegroup.timestamp > date("2022-07-25"))
:

IF JQL {type in standardIssueTypes()} AND In_Progress_Change.size() >= 1:
In_Progress_Change.size()

Please let me know if this helps! Looking forward to your feedback!!

Best,
David

[Alexander Bondarev]

Hi @David Niro !

Glad to see you! 

Thanks for the help, this is exactly what I need.

It is also very nice to receive not just a formula, but also recommendations for the future from representatives of the vendor-developer!

[David Niro]

Glad to see you as well!  You are most welcome, very happy that the solution worked for you!!