Structure Formula: Work Logged Column with User Properties for each worklog user in the column

[Kazimierz Tyczyński]

I am working with a Formula column on my structured table. In this Formula, I want to adjust the work logged to a numeric value. Ultimately, I would like to get a formula that takes the time from the Σ Worked Logged issue column in the structure. Logged time by user in the monthly range will multiply by the user attribute and number. Just like in the link below only that all in one field for all worklog.users who logged time. If it is too difficult to calculate in a single field then provide a column per usser as in the column "KT MD"

Links:

Group structure by worklog author 

Σ Worked Logged - sum over issue 

Customizing a Work Logged Column 

User Properties 

Formula:

• (worklogged.author * User Propertiey (orange) * number) between worklog.date < "01/01/2022" and worklog.date > date < "01/01/2022"

• formula result: Time calculated and totalled by all who logged time in (numerical result 0.00)

 Formula, use :

• worklogged.author
• Date - user in the monthly range 
• User Propertiey
• Number

Plugin:

• Structure v. 8.0.0 

[Kazimierz Tyczyński]

I will definitely use the earlier advice. I have another question. I wrote a formula as below image 2.

First question:

At the moment I am using another column for user1 which calculates the time for the month of January image 1. It is used in the formula under the variable "January" image 2. Can I write a Formula like in image 3. (User123456 - worklog.Author) and insert as a formula under the variable "January"?

Second question:

On image 2. Under the Seniority1 variable, I can retrieve the key-value from either worklog.author.property.car (JIRAuser) or user.property.car (JIRAuser)?

[David Niro]

Hello @Kazimierz Tyczyński ,

Glad to hear it!

I'm not sure I understand the first question.  Are you just trying to modify the values from the January column?  If so, I think the approach would be to next January as a variable within the formula from image 2.  You can next multiple formulas with a formula.

Regarding the second question, I'm also not sure about the goal here. Maybe the item property reference and Expr Language docs will help you determine if what you are trying to accomplish is possible.

Best,
David

[Kazimierz Tyczyński]

In the first question, I would like not to retrieve the worklog from the column but create a form from date to date and worklog.author (here I would like to select the author I want). Can I ask for an example?

[Kazimierz Tyczyński]

In the second question:

I saw the documentation item property reference  and  Expr Language unfortunately I cannot yet write what I want. I don't know how to call worklog.author.unique. I don't know how to enter the user ID number. I don't know how to combine worklog.author.unique with property key - value from jira. Please also provide an example

[David Niro]

Hi @Kazimierz Tyczyński ,

Sorry, I am still not sure I understand the goal, and can't provide an example.

Could you please describe your goal in a User Story format?  Something that would help me understand the business goal and what the different challenges and variables currently are?

Best,
David

[Kazimierz Tyczyński]

Below I send what you asked for @David Niro 

[Description of user].
Task 'Story' in structure. The task in the month of January logged 10h by user A, 5h by user B and 20h by user C. Total of 35h.

[Functionality]
In the new column type "formula" Count the logged time by user A , B, C multiplied by the user.property key and variable "number":

Logged time from 01.01.2022 to 31.01.2022 by User A = 10h
user A user.property.january = 0.6 and the variable "number" (0.82).

Logged time from 01.01.2022 to 31.01.2022 by User B = 5h
user B user.property.january = 0.5 and the variable "number" (0.82).

Logged time from 01.01.2022 to 31.01.2022 by User C = 20h
user C user.property.january = 0.4 and the variable "number" (0.82).

[Benefit] Numerical result of logged time in column formula:

Formula:
logged time from 01.01.2022 to 31.01.2022 by User A * user.property.january * variable "number" AND
logged time from 01.01.2022 to 31.01.2022 by User B * user.property.january * variable "number" AND
logged time from 01.01.2022 to 31.01.2022 by User C * user.property.january * variable "number"

(10h * 0.6 * 0.82) + (5h * 0.5 * 0.82) + (20h * 0.4 * 0.82) = 4.92 + 2.05 + 9.75 = 16.72

[David Niro]

Hello @Kazimierz Tyczyński ,

Probably the easiest way to accomplish something like this would be to add work logged columns for the users.  Then,  in a new formula column, use the CALENDAR_DAYS()  function to convert the values to the decimal format you are looking for Columns as Variables , make the extra calculation, and the CONCAT() function to combine the values with the names.

Something like this:

CONCAT(
"Tim: "; (Timsworkcolumn.CALENDAR_HOURS() * 3);
" Jim: "; (Jimsworkcolumn.CALENDAR_HOURS() * 3);
" Kim: "; (Kimsworkcolumn.CALENDAR_HOURS() * 3)
//etc
)

Where Tim, Jim and Kim's workcolumn is mapped to the work logged column you created for them.

You can delete the individual work logged columns after you've built your formula.  They will get stored to it directly.

Just be cautious and test it out on a staging environment to ensure that due to the number of users and work logs you are looking at you do not hit any performance issues.

Please let me know if this helps.

Best,
David

[Kazimierz Tyczyński]

@David Niro 

Thank you for your quick response. I will implement the idea you provided as soon as possible. I will get back with an answer as to whether it worked.

Best,

KT