work logged in past 7 days

Question

In structure, is there any efficient way to add a column that shows the sum of worklogged in the past 7 days?

I have tried using the standard work logged column, but it only allows selection of prior week, current week, or a fixed date range. I'd like the range to always be last 7 days without requiring setting the date range every day.

I've also tried pulling the worklogs in a Formula column as an array and filtering them as follows. But this seems to be extremely slow with a large number of issues/logs in my structure

worklog.FILTER( $.startDate >= DATE_SUBTRACT(TODAY(), 7, "day")).timeSpent

Is there any way to do this with the Manual Specification feature perhaps?

Manol Krastev · Answer

@David Niro ,

Is there a way to use functions in formulas - e.g. startOfWeek(), endOfMonth(), etc.

What I'm trying to achieve is a personal (currentUser()) time tracking for the current week, the past week, the current month, and the past month, however, those very same intervals are really hard to configure - impossible currently. :)

Here's my formula:
IF (worklogs.FILTER($.created >= startOfWeek(),
worklogs.FILTER($.author = ME()).MAP(w -> w.timeSpent).REDUCE((a, b) -> a + b)/3600000)

Any advise would be beneficial here. Thanks! :)

Regards,
Mani

hogan · Answer

Thanks David, I had tried that as well but it didn't seem to make a noticeable impact.

I have about 900 jiras in the structure, although, there are about 10k worklogs across them. So I'm guessing the slowness is due to the sheer number of logs.

However when using the worklogs column for "last week" it seems to popuplate very quickly, despite the number of logs

David Niro · Answer

Hello @hogan ,

David from ALM Works here.

At the moment, the only way to accomplish this would be to use a formula like the one you shared.

There is an adjustment we can try to see if it speeds up the calculation.

WITH past_seven =
  DATE_SUBTRACT(TODAY(), 7, "day")
:

worklog
  .FILTER( 
    $.startDate >= past_seven
  ).timeSpent

The idea here is that FILTER() is checking each work log for each issue.  When it does this, it was also calculating DATE_SUBTRACT(TODAY(),7,"day").  By moving the latter calculation to a WITH local variable, we are now making the calculation once per issue rather than once per work log per issue.

Depending on how many issues you have in the structure, it may still be a bit slow though.  If you wouldn't mind, could you please share how many issues you have in your structure?

Best,
David