How to use jql for missing Baseline end date calculations

Question

Want to know the JQL statement which can provide the result as the no. of ticket has missed the baseline end date, based on the Baseline End date & Resolution date.

I tried with the below statement but it's not giving results due to limitation of clause.

statusCategory = DoneAND resolutiondate > "Baseline End Date"kindly suggest the appropriate syntax for above. Thanks.

Thiago Wenceslau -Appfire- · Answer

Hi @jivan_patil,

I’m Thiago, a support engineer at Appfire.

If you are considering third-party apps, JQL Search Extensions (JQLSE) supports date comparison by default using the dateCompare() function.

Here is an example query:

issue in dateCompareIgnoreTime (“resolutiondate +1d < duedate +1w”) AND project = DEV

You can use any date fields in this query, and as shown in the example, even slide the values as you wish, adding or subtracting a period mathematically(m,h,d,w, M) being minutes, hours, days, weeks and months respectively.

Please contact our support if you have any other questions about this.

Best regards, Appfire support team.

Ivan Manolov _Appfire_ · Answer

@jivan_patil,

A different angle worth knowing about, if the staleness of a stored flag concerns you: instead of recording the verdict, you can have it worked out at the moment you look.

My team builds JXL for Jira, which puts an editable table over your Jira data and lets a column hold an expression. The one you'd want here is:

CALENDAR_DATE_TO_DATE(this.baselineEndDate) < this.resolutionDate

That conversion is doing real work. Jira date fields carry no time of day, so they have to be converted before you can sensibly compare one against a timestamp like Resolution date.

Filter the column to the late tickets and the row count is your answer. Alternatively, put the same condition into a conditional formatting rule and let the late rows stand out on sight:

Since it's evaluated on read, re-baselining a ticket changes the verdict straight away, with no rule to re-run and nothing to backfill.

Regards,

Ivan

Ivan Manolov _Appfire_ · Answer

Hi @jivan_patil,

The marker field plus Automation route is the right shape for this. One detail to check before you build it, because it silently inverts the result: the sign of the diff comparison.

Atlassian's own example is the giveaway. {{now.diff(issue.created).weeks}} returns -4 for an issue created four weeks ago, because when the second date is earlier than the first, the value comes back negative.

Applied to your case, {{issue.resolutiondate.diff(issue.Baseline End Date).days}} puts resolution first and baseline second. A ticket that missed its baseline has a baseline earlier than its resolution, so that expression is negative, and a greater than 0 condition would tag the tickets that finished early instead. Either use:

* {{issue.resolutiondate.diff(issue.Baseline End Date).days}} less than 0, or
* swap the operands: {{issue.Baseline End Date.diff(issue.resolutiondate).days}} greater than 0

I'd test the condition on two known tickets, one late and one early, and read the audit log before backfilling anything.

Two extras that tend to help here:

* businessDays works as a unit, so .diff(...).businessDays gives a lateness figure that ignores weekends. .abs and .prettyPrint are available too if you want the number stored in a readable form.
* Referencing the custom field by its ID rather than its display name is more reliable in smart values, particularly for names containing spaces.

Worth adding Field value changed: Baseline End Date as a second trigger as well. Otherwise a re-baselined ticket keeps a marker that no longer reflects the dates.

Best,

Ivan