nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

All comments

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

structure

addon-com.almworks.jira.structure

expr

formula