Group structure by worklog author

[rupert_schreder]

Hey together,
I am loading data into a structure by use of following filter:
timespent is not EMPTY AND worklogDate >= 2022-01-01 AND worklogAuthor in membersOfGroups(SolDevPA_Members)1

The group "SolDevPA_Members" contains all employees of affected department.

Target:
Inside a structure I want to show for each employee of the department how much work he/she has logged in last week, last month and this year...

Each row 1 employee, and the columns for sum of logged work by week, month, year.

Problem statement: The problem is that I can not group the structure by worklogauthor or user... I only can group by assignee what is not showing the correct worklog values as users are also logging work on subtasks/issues which are not assigned to them...

Any solution or idea how to solve this? How to group by worklogauthor?

Or any other ideas how to visualize this on 1 page?

[David Niro]

Hello @rupert_schreder ,

This is possible to do, and I have outlined the steps below.   Before you attempt this in your production environment, you will want to test this thoroughly in your staging environment to identify any performance issues. 

Depending on the number of issues in your structure and the number of work logs per issue, this may not be a viable solution as it can become a very heavy operation.   If you do see performance issues, please reach out to us directly via support@almworks.com or through our customer portal support.almworks.com

That being said here is how you can go about building this view in Structure:

Start with Group by Attribute and choose the Formula option.

For your Formula, use :

worklogged.author

At this point, you will most likely have duplicates in your structure since each issue will show up under every user that has logged work against it.  This is expected.

Now, we need to add three Formula Columns.  We will do these one at a time.

Work Logged Last Week:

IF itemtype = "user":
    VALUES#children{ _worklogs }
        .FILTER($.author = this.name)
        .timespent
        .SUM()

under the Variable List, select _worklogs and use the formula below:

with _today = today():
with _days =
    weekday(_today)+6:
with _startoflastweek =
    date_subtract(_today, _days, "day"):
with _endoflastweek =
    date_subtract(_today,_days-4,"day"):
with _lastweek =
    ARRAY(_startoflastweek,_endoflastweek):

worklogs.FILTER($.startdate >= _lastweek.FIRST() AND $.startdate <= _lastweek.LAST())

Work Logged Last Month:

IF itemtype = "user":
    VALUES#children{ _worklogs }
        .FILTER($.author = this.name)
        .timespent
        .SUM()

where _worklogs is:

with _today = today():
with _days =
    weekday(_today)+36:
with _startoflastmonth =
    date_subtract(_today, _days, "day"):
with _endoflastmonth =
    date_subtract(_today,_days-30,"day"):
with _lastmonth =
    ARRAY(_startoflastmonth,_endoflastmonth):

worklogs.FILTER($.startdate >= _lastmonth.FIRST() AND $.startdate <= _lastmonth.LAST())

Work Logged This Year:

IF itemtype = "user":
    VALUES#children{ _worklogs }
        .FILTER($.author = this.name)
        .timespent
        .SUM()

where _worklogs is:

with _today = today():
with _days =
weekday(_today)+309:

with _startofyear =
date_subtract(_today, _days, "day"):

worklogs.FILTER($.startdate >= _startofyear)

Let me know if this helps!

Best,
David

[rupert_schreder]

Hey David,

thx a lot for the answer.. All set up as suggested above, but currently I have following issues:

• Data format of columns: What is the unit of the shown values and how can I format them to basically hours?
• Timespan: Would be better to see worklog in current week, in current month and in this year... (not the last 7 days/30days as currently suggested, instead sticking on the calendar week/month)
• Shown data: This is the main prob, the shown data does not match to timesheet report.. e.g. users have empty data in the structure lastWeek column, but when I compare with JIRA Timesheet report there is some work logged... but this does not show up in structure column... Any ideas?

Data is still loaded by use of my initially mentioned JQL query.

Hope you still can help here!

Thanks in advance!

Rupert

[David Niro]

Hello @rupert_schreder ,

• Data format of columns:  you can change the option from General to Duration.  This will format the value you see to the duration you are looking for.
• Timespan:  I see I misunderstood here "I want to show for each employee of the department how much work he/she has logged in last week, last month and this year...".  The formulas do not return a trailing 7, 30 days, etc.  Take for example Last Week formula.  It is determining the start and end of the previous week (at the time of posting, this would be November 14 and November 18).  That being said, the calculation does not seem to scale well for Month or year, so we will need to find an alternative.
• Shown data:  For this we would need to look at some actual work log records in the Jira issue history to determine what is being missed.  I would expect some missed items in Last Month and This Year (as mentioned above), but Last Week should work.

Please reach out to us directly at support.almworks.com or via email support@almworks.com.

Best,
David

[David Niro]

@rupert_schreder ,

Re Timespan Month and Year calculation:

For This Month , please use the formula below for _worklogs

with _today = today():
with _monthstart = START_OF_MONTH(_today):
with _monthend = END_OF_MONTH(_today):
with _thismonth = ARRAY(_monthstart,_monthend):

worklogs.FILTER($.startdate >= _thismonth.FIRST() AND $.startdate <= _thismonth.LAST())

And for This Year, _worklogs should be:

with _currentyear = YEAR(today()):
with _startofyear = MAKE_DATE(_currentyear,01,01):

worklogs.FILTER($.startdate >= _startofyear)

Please let me know if this works for you.  I will keep an eye out for your ticket regarding worklogs not showing for last week.

Best,
David

[rupert_schreder]

Thanks David, not yet working as intended... I reached out to you as suggested via email. to define next steps.

Thx.

[Mykola Bilenko]

@David Niro @rupert_schreder Hey guys, I'm interesting in this topic too. So, what's the results of yours discussion in DM and could you share me final decision, please.

[David Niro]

Hello @Mykola Bilenko ,

The formulas below were the end result. I do want to make sure that I reiterate that these are all potentially VERY heavy operations in their own right, so I would recommend testing that they work well together without performance issues in a staging environment before adding them to your production instance of Jira.

That being said, the Last Week formula will look like this:

IF itemtype = "user":
    VALUES#children{ _worklogs }
        .FILTER($.author = this.name)
        .timespent
        .SUM().JIRA_HOURS()

where _worklogs is:

with _today = today():
 with _days =
 weekday(_today)+7:
 with _startoflastweek =
 date_subtract(_today, _days, "day"):
 with _endoflastweek =
 date_subtract(_today,_days-6,"day"):
 with _lastweek =
 ARRAY(_startoflastweek,_endoflastweek):
worklogs.FILTER($.startdate > _lastweek.FIRST() AND $.startdate < _lastweek.LAST())

This Month:

with _today = today():
with _monthstart = START_OF_MONTH(_today):
with _monthend = END_OF_MONTH(_today):
with _thismonth = ARRAY(_monthstart,_monthend):

worklogs.FILTER($.startdate >= _thismonth.FIRST() AND $.startdate <= _thismonth.LAST())

This Year :

with _currentyear = YEAR(today()):
with _startofyear = MAKE_DATE(_currentyear,01,01):

worklogs.FILTER($.startdate >= _startofyear)

Best,
David

[Mykola Bilenko]

@David Niro 

Hi, David. What if I will expand requirements and I ask you about similar.

How can I count through filtering amount vacation days for each month?

I need to notice that I don't use worklog, but I have DateStart and DateEnd dates expressed in custom fields. For example, If I have today one day of vacation I just add issue with DateStart ("2023-03-22") and DateEnd ("2023-03-22").

The most difficult thing is Vacation which started in one month and repeated in the next one.

Look below what I'm using now.

Calculation of raw days:

sum#all{
WITH raw_days = days_between(datestart, dateend) + 1 :
WITH weeks = floor((raw_days + weekday(datestart)) / 7) : 
raw_days - weeks * 2
}

Fixed dates of month:

sum#all{
with _today = today():
with _monthstart = DATE("2023-01-01"):
with _monthend = DATE("2023-02-01"):
with _thismonth = ARRAY(_monthstart,_monthend):

 Thank you for any advices!