JQL to find a status change during beginning date through 2 weeks

[Jay Whitman]

I'd like to have a JQL filter that looks for all items whose status changed within a 2 week time frame starting with a specific date.  I know I can enter two specific dates, but, since I need to change this filter every two weeks, I'd like to just change the first date.

This is what I currently have:

status changed to Closed during (2024-11-07, 2024-11-20)

But what I'd like to do is something like:

status changed to Closed during (2024-11-07, +2w)

Hopefully that makes sense.

Any suggestions?

[Trudy Claspill]

Hello @Jay Whitman 

What you have can be used if you remove the "+" character.

What are you doing with the output of the filter?

You could create a Saved Filter and Subscribe to the saved filter to automatically receive the output every two weeks on the day of the week that corresponds to the dates you are using in the first position. Then you could use a filter like this:

status changed to Closed after -2w

[Jay Whitman]

Hi @Trudy Claspill 

I thought the -2w represented a date 2 weeks prior to the current date?  If so, that's not really what I'm looking for.

I'm looking to have a 2 week date rage based on the first date entered.  So, if the first date entered is 2024-11-07, then I want the "during" time frame to be from 2024-11-07 to 2024-11-20.  

I'm going to need to change this filter every two weeks but the date I change it might not always be the same day of the week.  So I need the flexibility to be have a specific date range.  And there may be other times when I need to choose a 2 week time frame further back in history.

Thus, being the lazy person I am, I'd like to be able to change just one date rather than figure out what the second date is (it'll always be 2 weeks after the first date) and change it as well.

[Trudy Claspill]

I thought the -2w represented a date 2 weeks prior to the current date?  If so, that's not really what I'm looking for.

Yes, it does. And if you wanted to get the output automatically every Monday, for example, for the prior two weeks, then using this with a saved filter to which you subscribe to receive the output every two weeks on Monday would satisfy the requirement. That is, only if you want the report on a repeating schedule.

If you want to manually change the starting date and then get the data for the two weeks following the specified date, and you need flexibility on that starting date, then you would use the "during" predicate with the date and then "2w" (without the plus sign) for the second parameter.

[Jay Whitman]

Thanks again Trudy.

Unfortunately using "2w" doesn't seem to do what I wanted.

I ran the following JQL and it returned 25 items.

status changed to Closed during (2023-11-07, 2023-11-20)

Then I ran this query just replacing the second date with "2w" and got 194 items.

status changed to Closed during (2023-11-07, 2w)

So the "2w" doesn't seem to represent a date 2 weeks after the first date.  Bummer.

Definitely not a show stopper but doesn't quite fulfill the purpose for which I was trying to achieve. 

[Trudy Claspill]

Did you compare the lists , identify the missing issues, and examine the history of each one to determine the date of the transition? It may be that you need to use 14d or 15d instead of 2w.

[Jay Whitman]

Well, the list comparison I did was how many items returned. LOL

So I just tried 14d and 15d and 1w and all of them returned the exact same number of items: 194  So I tried enclosing those values in quotes and the query still returned 194.

So maybe our Jira setup is different than what you are referring to and using 2w, 14d, etc. isn't set up correctly.  Oh well.

[Trudy Claspill]

I did some more testing with a start date that was more than the specified number of weeks in the past; i.e.

status changed to Done during ("2024/10/07",3w) order by key asc

Like you I found that the results of the above was more issues than if I replaced "3w" with the actual date three weeks from the start date.

So my suggestion is invalid.  

I have not found a way to do a relative end date like you want.