JIRA filter problem

[Nunzio]

Hi guys,

when doing a report for the last year on our project we get different statistics.
For the whole year we get 482 tickets.
When we go on each month we get the total 461.

The query used is for the whole year is:

project = ENT AND component = "02 - NETWORK" AND created >= 2018-12-01 AND created <= 2018-12-31 ORDER BY created ASC, updated DESC

The query used for each month is ( example for December ) :

project = ENT AND component = "02 - NETWORK" AND created >= 2018-12-01 AND created <= 2018-12-31 ORDER BY created ASC, updated DESC

What's wrong? :(

Thanks for you're help

[Mario Carabelli · Accepted solution]

@Nunzio

On the first view the queries look sound. 

Can you send the other monthly queries.Than I can double check them.

Maybe you entered a wrong end of the month (30. instead of 31.) somewhere or got the logic wrong  ( <= instead of if <) or something similar.

Regards

Mario

[Nunzio]

Hi Mario, 

are the same.. I change just the date. 

I've already done a check and I'm sure to don't type wrong end of the month.. to avoid this problem I've also used the basic mode to create the filter :(

[Mario Carabelli]

Okay.

Then I don't know theanswer.

But you could try to isolate the excess issues in the yearly query  with:

Filter = "name of the yearly filter" AND NOT (Filter ="name of january filfer" OR Filter = "name of february filter" OR...)

Maybe this can throw a spotlight on the underlying issue.

With kind regards

Mario

[Nunzio]

Thanks again Mario!

I've saved 3 filters:
1 - project = ENT AND component = "02 - NETWORK" AND created >= 2018-12-01 AND created <= 2018-12-31 ORDER BY created ASC, updated DESC

2 - project  = ENT AND component = "02 - NETWORK" AND created >= 2018-01-01 AND created <= 2018-01-31 ORDER BY created ASC, updated DESC

3 - project = ENT AND component = "02 - NETWORK" AND created >= 2018-01-01 AND created <= 2018-12-31 ORDER BY created ASC, updated DESC

Below the result :( 

I'm doing anything wrong :(

Thanks for your patience!!

[Mario Carabelli]

Hi @Nunzio

I checked it and it seems like i gave you faulty jql( i answer community questions on my phone so i can't check them in our own jira). I am sorry for tgat.

It seems like NOT can't be used as single operator but only in conjunction with IN.

Fortunately we should be able to make this work with:

Filter ="Filter_year" AND Filter NOT IN ("Filter_jan", "Filter_feb",..., "Filter_dec")

Let me know of the results.

Mario

[Nunzio]

HI @Mario Carabelli,

don't worry :) 

ok now with your query I found out the 21 ticket missing.

Are all tickets of the last day of the month.. but why happen if in the query I type <= ??

project = ENT AND component = "02 - NETWORK" AND created >= 2018-06-01 AND created <= 2018-06-30 ORDER BY created ASC, updated DESC

Thanks as usual for your help

[Nunzio]

Ok I've understood the problem! 

I've to put also the time!! 

With this filter: created >= 2018-06-01 AND created <= 2018-06-30" the filter take everything from 06/01 12:00AM to 06/30 12:00AM.

That will miss all issues created on 06/30 after 12:00AM !!!

[Nunzio]

https://community.atlassian.com/t5/Jira-questions/Search-between-two-dates-doesn-t-seem-to-be-inclusive-and-is/qaq-p/266367

[Mario Carabelli]

Hi @Nunzio,

great that you found the problem. This 12am thing is good to know.

It was nice working with you ☺️

With kind regards

Mario

[Nunzio]

Thanks again for all! 

Was nice also for me :)

Kind regards

Nunzio :)

[Mario Carabelli]

Don't forget to mark your answer as accepted so that other people can find the solution more easily.

With kind regards

Mario

[Christos Moysiadis]

Hi @Nunzio,

can u use this ? 

project = ENT AND component = "02 - NETWORK" AND created >= startOfMonth() AND created <= endOfMonth() ORDER BY created ASC, updated DESC

the above jql will count the issues of the current month though :/

& this

project = ENT AND component = "02 - NETWORK" AND created >= startOfYear() AND created <= endOfYear() ORDER BY created ASC, updated DESC

best regards

CM

Answer update.... if you use the <= or >= you are "losing" some issues as i see.. try to use a JQL like

project = ENT AND component = "02 - NETWORK" AND  created > "2018-11-30" and created < "2019/01/01" ORDER BY created ASC, updated DESC  ... :/

[Mario Carabelli]

@Christos Moysiadis

You can use this jql functions with arguments to report on other months.

For example startOfYear(-1M) to mark the start of the last month in the last year.

But these functions are always dynamic or respectively relative to the system time.

But for the purpose of yearly reports I would actually go with @Nunzio queries. Just because you can use the same filters in a year and they will deliver the same results.

The error which must lay in some little detailand is independent of this aspect though.

Kind regards

Mario

[Nunzio]

Hi Christos,

thanks for you're reply. 

Yes I can but how can help me these 2 query? I mean, the first one is for the current month and the second is for the current year. 

I need to do a report of 2018 

Can you help me?

Sorry if I bother you and thanks again

[Christos Moysiadis]

@Mario Carabelli thank you for your note ;) i didn't know that i could use -1... thats great!

Anyway i can not think another possibility ! ill check here and wait for your solution :P 

Best regards

CM

[Mario Carabelli]

My pleasure☺️

[Christos Moysiadis]

@Nunzio are you sure that these issues have the component that you describe?

Because u have 3 different things that u are searching for!

A= project

B= component

C= time period 

I can not understand how the filters fail !!! 

best regards