Expand my Community achievements bar.

SOLVED

Sum all Planned Hours and convert to %

Avatar

Level 3

Hi WF Community,

 

I am seeking help with my code. I wanted to sum all planned hours and aggregate to a percentage, but it was not showing me any results Might be a syntax error. Here's my code.

 

aggregator.function=SUM
aggregator.valuefield=estCapacity
displayname=EST Capacity
textmode=true
valueexpression=DIV(SUM({workRequired}),160)
valueformat=HTML

A_EN_0-1707918636922.png

 

1 Accepted Solution

Avatar

Correct answer by
Level 5

Ah, I see. I think you want to use 9600 instead of 160. It's my understanding that most time-related calculations in Workfront are done in minutes, not hours (even though the field says planned "hours"). So, 60 minutes * 160 hours = 9600.

Try this:

aggregator.displayformat=val
aggregator.function=SUM
aggregator.namekey=estCapacity Calc
aggregator.valueexpression=ROUND(DIV(SUM({workRequired}),9600)*100,2)
aggregator.valuefield=ROUND(DIV(SUM({workRequired}),9600)*100,2)
aggregator.valueformat=val
displayname=EST Capacity
textmode=true
valueexpression=ROUND(DIV(SUM({workRequired}),9600)*100,2)
valueformat=val

View solution in original post

8 Replies

Avatar

Level 5

Give this a try:

aggregator.displayformat=doubleAsPercentRounded
aggregator.function=SUM
aggregator.namekey=estCapacity Calc
aggregator.valueexpression=ROUND(DIV(SUM({workRequired}),160)*100,2)
aggregator.valuefield=ROUND(DIV(SUM({workRequired}),160)*100,2)
aggregator.valueformat=customNumberAsPercentRoundedString
displayname=EST Capacity
textmode=true
valueexpression=ROUND(DIV(SUM({workRequired}),160)*100,2)
valueformat=customNumberAsPercentRoundedString

Avatar

Level 3

Hi @J_Mas ,

 

I really appreciate your response, but I have a question: why does the percentage go beyond 100%? and when I tried to calculate in Excel, it did not give the same percentage as Excel.

A_EN_0-1707933355893.png

 

Avatar

Level 5

Hey @A_EN , can you provide some additional details as to what you're trying to accomplish? You said that you're trying to Sum all planned hours and aggregate as a percentage. My questions are:

  • At what level (object type) are you trying to SUM the planned hours? (e.g. Project, Task, User, etc.)
  • What is the percentage meant to show?

For instance, are you trying to sum all the planned hours in a project (at the task level), and then the percentage would show the percent of planned hours that an individual task represents of the total project? (e.g. maybe the planned hours of a "Create Draft" task makes up 5% of the project's total hours).

Or, maybe you're trying to sum the total planned hours of a specific user, and then the percentage would show the percent of planned hours that an individual task represents of that user's total planned hours?

I ask because depending on what you're trying to represent, the formula may need to be adjusted.

Avatar

Level 3

HI @J_Mas ,

 

Sorry for the confusions. I'm just trying to have a simple calculation of the task reflecting this formula: percentage = total planned hours of the subtask / 160. 

For example, if user 1 has a total of 70 hours on his task, I wanted to have that aggregated in percentage. My formula will be % = 70/160, and the answer will be 44%.

 

A_EN_0-1707934575843.png

 

Avatar

Level 3

Hi @J_Mas ,

 

160 is the total number of hours in a month. We multiply 40 hours per week by four to get 160.

Avatar

Correct answer by
Level 5

Ah, I see. I think you want to use 9600 instead of 160. It's my understanding that most time-related calculations in Workfront are done in minutes, not hours (even though the field says planned "hours"). So, 60 minutes * 160 hours = 9600.

Try this:

aggregator.displayformat=val
aggregator.function=SUM
aggregator.namekey=estCapacity Calc
aggregator.valueexpression=ROUND(DIV(SUM({workRequired}),9600)*100,2)
aggregator.valuefield=ROUND(DIV(SUM({workRequired}),9600)*100,2)
aggregator.valueformat=val
displayname=EST Capacity
textmode=true
valueexpression=ROUND(DIV(SUM({workRequired}),9600)*100,2)
valueformat=val

Avatar

Level 3

This solves my questions Thank you so much @J_Mas Really appreciate your help here!