Sum all Planned Hours and convert to %

[A_EN]

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

[J_Mas]

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

[A_EN]

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.

[J_Mas]

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.

[A_EN]

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%.

[J_Mas]

What does the 160 represent?

[A_EN]

Hi @J_Mas ,

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

[J_Mas · Accepted solution]

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

[A_EN]

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