Graph tickets over time logic not working correctly

[Michiko Chand]

I want to graph unresolved issues over time to get the correct number of issues on a given date but the logic is not working correctly.

Here is my JQL:

project=ESN and issuetype=Bug and status was not in (Done, Closed, Deployed)

What is happening is that for every date that the graph has to be plotted on the following query is run e.g.

to plot on 6/30, the query that the gadget uses by appending During is

project=ESN and issuetype=Bug and status was not in (Done, Closed, Deployed) during ('2021/01/01','2021/06/30')

to plot on 7/31, the query that the gadget uses by appending During is

project=ESN and issuetype=Bug and status was not in (Done, Closed, Deployed) during ('2021/01/01','2021/07/31')

However this logic is not correct since the result is also including tickets created after 6/30 and 7/31 in the count.

The correct query that should be executed for each date should be:

to plot on 6/30 and get a correct issue count, the query should also append a “created < date” clause and should be

project=ESN and issuetype=Bug and status was not in (Done, Closed, Deployed) during ('2021/01/01','2021/06/30') and created <='2021/06/30'

to plot on 7/31 and get a correct issue count, the query should also append a “created < date” clause and should be

project=ESN and issuetype=Bug and status was not in (Done, Closed, Deployed) during ('2021/01/01','2021/07/31') and created <='2021/07/31'

How can this be addressed so i get the correct count?

[Michiko Chand]

Any updates on this?

[boydo]

Hi Michiko,
Sorry for the delay. I maintain this in my spare time, so don’t check in that often.

Is there any improvement if you invert the logic related to the status?
Instead of checking for status not in a list , instead check for a status in a list?
I’m wondering if issues are being returned from the JQL search, even if they were created in the future?

[Michiko Chand]

Thank you for your response! I appreciate you taking the time to create and maintain the repo.

Instead of checking for status not in a list , instead check for a status in a list? - This will not work since I need a point in time snapshot. It will bring in tickets which have been closed as well.

I’m wondering if issues are being returned from the JQL search, even if they were created in the future? - Correct.

I know that in the logic you append a “ during ('graph start time','current plot time')” to the query. If you also append and created <='current plot time' , this will be solved.

[boydo]

Michiko,
Yes, I will add in an option to append that to the query if that’s the only solution available here.

But, when I try it on my test instance switching to an allow list of statuses as I describe fixes the issue.
I think they have a weird status that is not in a list, but if the check is to see if they are in a list then they don’t show up.

A simple, quick way to test would be via a JQL query. Can you double-check to make sure it doesn’t work for you with an in query please?

[Michiko Chand]

I have tried the logic and it does not work. Here is why:

in list query would be:

project=ESN and issuetype=Bug and status was in (Open, In Progress, In Review)

Say we are plotting a monthly graph between Jan - May, the logic for each graph point would be -

project=ESN and issuetype=Bug and status was in (Open, In Progress, In Review) during ('graph start time','current plot time')

Now if a ticket moved to “Done” status in March, it would still show up in the results for April and May since it was in (Open, In Progress, In Review) status between Jan-Feb. The result is incorrect.

Thank you for considering to add this enhancement.

[boydo]

Hi Michiko,
I think there’s some confusion here. The app doesn’t append … during ('graph start time',current plot time')
To use your example it would run 6 individual queries (which you can check in the network tab of the Chrome debugger) which would be:

… during ('01/01/2021', '31/01/2021')

… during ('01/02/2021', '28/02/2021')

etc.
ie. a separate query for each individual month, and not including data from every month up to the current plot time

I’m assuming that your chart configuration includes:
- Datasets → Time Series Field = “None (Sum of existing issues)”
- Chart Dates → Period = “Monthly”

[Michiko Chand]

First I would like to thank you again for your collaboration on this

during ('01/01/2021', '31/01/2021') - This would also give incorrect results. Since tickets which moved to “Done” during 01/02-01/31 would show up in the query.

Chart configuration is as you mentioned.

[boydo]

Michiko,
Apologies for the confusion. Hopefully I’m understanding correctly …

tickets which moved to “Done” during 01/02-01/31 would show up in the query

This is expected behaviour, because the ticket was also in one of the other states during the month.
It sounds like you only want issues that were in a specific state at the end of the month. Have you tried reducing the chart period to “weeks”, or “days”? That might give you a more accurate visualisation of the data?

Also, FWIW I’m no longer sure that doing this as you requested:

If you also append and created <='current plot time' , this will be solved.

actually helps solve the problem regardless of the query used, as there’s no guarantee that the issue will always be created when you need it to be (or, at least there’s no guarantee for the other users of this app)

[Michiko Chand]

It is expected behavior of the query but it does not solve the business case that I need to report on which is “How many tickets are still open at the end of the month”?

Hence I need to use the not in condition vs. the in condition

Have you tried reducing the chart period to “weeks”, or “days”? That might give you a more accurate visualisation of the data? - It brings me closer to the data but still not accurate data.

When I use the in condition and append and created <='current plot time', I get accurate results.

Appending and created <='current plot time' should work for all customers since you don’t want to plot any tickets that are not created yet.

[boydo]

Michiko,
Thank you very much for your patience. I believe I finally understand!

I’m a bit worried about adding created <= ‘current plot time’ to the queries because it may affect results of existing charts, and trigger more feature requests related to tweaking the extra clause

My preference would be to support this via the use of $START and $END fields in the JQL query (for the start and end of each time period) and delegate how they are used to the end user.
So your query would then be:
project=ESN and issuetype=Bug and created <= $END and status was not in (Done, Closed, Deployed)

I made this change and deployed it. Would you please see if it helps your case?

[Michiko Chand]

David:

This is working perfectly. Thank you very much for your support and cooperation.

Thanks,

Michiko

[boydo]

No probs!