How to to filter issues for last two weeks (starting Sun- ending Sat)

[AT]

Hi, I am looking to look at team velocity for every two week duration going for last week (starting on Sun and ending on Saturday). Currently, I am using following query which tells me when an issue was deployed on production as indicated by state change. I need to be able to filter out this state change for last two weeks (starting Sunday and ending Saturday)

Another question:
Also, does this filter out last week (Sun-Mon) issues with the given state change?
project = <projectname> AND status changed FROM "Reviewing" TO "Pending Closure" DURING (startOfWeek(-1), endOfWeek(-1)) ORDER BY updated ASC

[Walter Buggenhout · Accepted solution]

Hi @AT,

before diving into your query, if you use Jira Software and use a Scrum Board, there is a velocity report out-of-the-box that displays your team's velocity across sprints. If you work in sprints that start on Sunday and end 2 weeks later on a Saturday, then your velocity report will display exactly what you seem to be looking for.

Looking into the query you designed, you are using startOfWeek() and endOfWeek() functions to retrieve issues that had a status change somewhere in the past. That could work, but here's how to use them properly ...

startOfWeek() returns the first day of the current week. That may be a Monday or a Sunday, depending on how your Jira instance is configured. In your example, startOfWeek(-1) will return the first day of the week before the current week. And the same goes for endOfWeek(-1), which is returning the last day of the previous week.

WIth the following JQL, you can add or substract individual days from this start or end of week functions to get the desired result:

created > startOfWeek(-2d)

 This will retrieve all issues that were created after the start of the day that is 2 days before the first day of the current week. As soon as you know what is configured as the first day of the week in your Jira instance, you should be able to figure out what you need to find the right formula.

Hope this helps!

[AT]

Thank you Walter! It does help me understand the concept of the functions better. We are not using Scrum Board anymore. Our team transitioned from using Sprints and is now doing continous delivery so we have opted to use Kanban board instead.

We are still using story points for project planning and it is a nice to know metric as to how much our team has delivered in two week duration and see if our velocity has gone up or down with our transition to Kanban.