Structure Formula - Filter Worklogs by Date Range

[James Roy]

Hi there,

First time posting. Not a dev. Extreme newb to expression formula.

I have a structure with issues and subtasks that have work logs against them.

I would like to sum the work logged between a certain date range using the Jira Structure formula column.

This unexpectedly returns values but the .filter operator is wrong "< sprintStart":

/* Displays work logged 
between a specified date range.
*/

WITH sprintStart =
       date("27/03/2023") :

WITH sprintEnd =
       date("11/04/2023") :

WITH workLogged =
       worklogs
            .FILTER( $.startDate < sprintStart )
            .FILTER( $.startDate < sprintEnd )
            .timeSpent.SUM() :

       jira_days(workLogged)

This however, with the correct operator returns nothing "> sprintStart":

WITH workLogged =
       worklogs
             .FILTER( $.startDate > sprintStart )
             .FILTER( $.startDate < sprintEnd )
             .timeSpent.SUM() :

       jira_days(workLogged)

Any help would be great even a whole new direction would be welcome. I know there is a Work Logged column available but I have to use the formula column in this instance.

Cheers,
James

[David Niro]

Hello @James Roy ,

Welcome to the Community!

Apologies for the delay.

I believe it's the Date format that is causing a problem here.  It looks like you are using dd/mm/yyyy.  Try mm/dd/yyyy, it worked for me in my testing.

Please let me know if it helps!

Best,
David

[James Roy]

Hi @David Niro ,

Thanks for getting back to me. I ended up using the .Filter a little differently. Perhaps trying to use two was causing an issue but the following seemed to work for me.

WITH endDate =
DATETIME("9/May/2023 12:00 AM", "en_AU", "Australia/Melbourne")+86400000 : /*end date plus 1 day*/

WITH startDate =
endDate - (86400000 * 13) : /*end date minus 13 days*/

WITH workLogged =
worklogs
.FILTER( $.startDate > startDate AND $.startDate < endDate ) 
.timeSpent.SUM() :

CEILING(jira_days(workLogged),3)