Using the resolution date filter in the right way

[Simon von Pock]

Heya,

I prepare a report every month concerning the performance of your scrum team. Additionally, I try to compare the numbers of 2020 to the years before. In order to do that, I use a filter on the resolution date.

So during the year I use resolution date filters like for example 'resolved >= 2020-03-01 AND resolved <= 2020-03-31' in order to prepare the report for march. At the end of the year I use the filter like that: 'resolved >= 2020-01-01 AND resolved <= 2020-12-31'

Problem: the results of the second filter exceeds the sum of the results of the individual months.

What am I doing wrong?!

[Jack Brickey]

TBH it is hard to say. I would step back from the numbers and rather export all issues from all filters into CSV and then look to Excel/Sheets to find where the delta is and then inspect them which should lead to the smoking gun. One other thing... you might consider this annual query

resolved >= startofyear() and resolved <=endofyear()

and if you run it in the following year and want previous year...

resolved >= startofyear(-1) and resolved <=endofyear(-1)

also consider using "resolutiondate" to see what it yields.

[Sandra Enqvist]

1

[Joseph Johnston]

I added this in case some of you have created and closed tickets in an administrative way after the fact. While not ideal, it is sometimes the case. In this situation **JIRA has a bug**

If you manually set the closed date the filter will still use the ACTUAL date you resolved the ticket.

EXAMPLE: Looking at the ticket in JIRA WEB

Joe Johnston made changes - 20 minutes ago
Resolution Date 07/Mar/2023
Resolution Done [ 10000 ]
Status Production Validation [ 12007 ] Done [ 10004 ]

Excerpts from the same ticket's xml export here you see resolved is not represented in the data as any rational person might expect:

 
<created>Mon, 3 Apr 2023 09:13:14 -0400</created>
<updated>Mon, 3 Apr 2023 09:15:32 -0400</updated>
<resolved>Mon, 3 Apr 2023 09:15:32 -0400</resolved>
...
<customfieldvalues>
<customfieldvalue>Tue, 7 Mar 2023 00:00:00 -0500</customfieldvalue>
</customfieldvalues>

(Given = it is now April) Searching for tickets using the following

resolved < endOfMonth(-1) AND resolved >= startOfMonth(-1) 

or

ResolutionDate < endOfMonth(-1) AND ResolutionDate >= startOfMonth(-1) 

this ticket will NOT show up until I do the May run. It's just broken. No matter how JIRA does it behind the curtain JIRA's own filters should be able to decipher it's own structure.

I hope I am wrong and I can be corrected. 

[Yosef Arbiv]

Deleted

[Jack Brickey]

Hi @Simon von Pock , quotes are not required for dates in a JQL. Are you experiencing something different with and without quotes?