Report Custom Column Aggregate Incorrect Value

[NickVa1]

Hi WF Community,

We're trying see an aggregate rollup at 2 grouping levels (parent task and project) for Hours Burned.  We're close to accomplishing this with text mode (aggregator), but the numbers (percentages) are a little off.

Here's what we have so far:

• The Hours Burned (Parent) column is a percentage based on Total Billable Hours - Task / Planned Hours
• As you can see, the percentages in the Hours Burned (Parent) column are incorrect
• If I unravel the Parent Name sub-grouping, each task is a row, and the Hours Burned (Parent) values are correct - they just don't roll up to the grouping levels accurately

Here is the text mode we're using for the Hours Burned (Parent) column:

aggregator.displayformat=doubleAsPercentRounded
aggregator.function=AVG
aggregator.namekey=Total Billable Hours - Task
aggregator.valueexpression=CONCAT((ROUND((DIV({DE:Total Billable Hours - Task},({workRequired})/60)*100),2)),"%")
aggregator.valuefield=DE:Total Billable Hours - Task
aggregator.valueformat=doubleAsPercentRounded
displayname=Hours Burned (Parent)
textmode=true
valueexpression=CONCAT((ROUND((DIV({DE:Total Billable Hours - Task},({workRequired})/60)*100),2)),"%")
valueformat=Compound

If anyone knows where we're going astray, your assistance would be appreciated.
Thanks,

Nick

[Rafal_Bain · Accepted solution]

Hi Nick,

I don't consider myself an expert, but I would try following:

1. create calculated field with your formula

2. add this field to your report

3. apply AVG on column settings

My experience is that some calculations that work fine as calculated fields in custom forms won't work when used in creating view, and certainly some (or all calculated columns) cannot be used for creating groupings.

If you happen to try this out, or you know that my approach wouldn't I would be happy to learn about this

Best

Rafal