Calculated number field using relative dates?

[Peter Daniledes]

Is there any way to use a calculated number field to display the number of issues that have a custom date field value in a certain range? For instance, the ability to show how many issues have a custom field date in the current week, another field to show the number of issues with a custom field date in the next week, etc. From the documentation, I could only find a way to specify specific dates, but not relative ones.

[Fidel Castro Armario]

Hi @gpd5,

We can do many relative calculations with time, but in particular test whether a date field has date value in current week (i.e., between current week's Monday and Sunday) requires a very complex expression.

Your requirements can be easily implemented by adding some new functions to the parser like: beginningOfWeek() and endOfWeek(). I will try to add them to next version of the add-on.

Can I help you with any other expression?

[Peter Daniledes]

I think that's all we're looking for, thank you - adding those functions would be great! Would we have the capability to specify the day considered the start/end of the week so that we can specify Sunday or Monday as in JIRA, and also to specify the start of future weeks as in JIRA?

[Fidel Castro Armario]

Hi @gpd5,

How do you specify the start of future weeks in JIRA? Are you doing it using a JQL query? Please, provide an example.

[Peter Daniledes]

It's supported by the startOfWeek and endOfWeek functions: https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-functions-reference-764478342.html#Advancedsearching-functionsreference-startOfWeekstartOfWeek()

An example would be: category = Configuration AND "Planned Start Date" >= startOfWeek(8d) AND "Planned Start Date" < endOfWeek(8d) ORDER BY cf[10205] ASC to search for next week starting on Monday, or we could replace "8d" with "1" or "+1" to specify starting at the next week on whatever the natural day for that region is (here in the US, it defaults to Sunday)

Does that help?

[Fidel Castro Armario]

Hi @gpd5,

As an example: If you want to create a field to show how many issues have duedate in the current week you can use "Calculated Number Field" with the following configuration:

Math Expression is:

count(issuesFromJQL("duedate >= startOfWeek() AND duedate <= endOfWeek()"))

[Peter Daniledes]

And I can do that with a custom field, and for weeks past the current week?

[Fidel Castro Armario]

Hi @fcarmario,

Yes, you can. You simply have to enter a valid JQL query as argument of function issuesFromJQL().

Replace duedate with the name of the custom field. If it has spaces, you should write it double quotes, but you should escape the " characters like this: \".

Example: count(issuesFromJQL("\"My Field\" >= startOfWeek() AND \"My Field\" <= endOfWeek()"))

For using weeks past current week, you should use syntax described at: https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-functions-reference-764478342.html#Advancedsearching-functionsreference-startOfWeekstartOfWeek()

[Peter Daniledes]

Okay thank you, I think that will accomplish what we need!

[Fidel Castro Armario]

I close the issue, then.