Report grouping aggregator %

[NickVa1]

Hi WF Community,

I noticed that when I have a field that contains a value as a percent, when I include it in a report and want to get a percent value at the grouping level it's wrong.

Example:

When I use the "Average" option on the 'Sum this column by' options for the '% On Hours Used' column, it just takes the averages for the field values on each record under the grouping.  When in my example above, if you divide the 'Total Actual HRS PRGM' field value by the 'Sold Hours' value, it's the correct value I'm looking for.  So to clarify, I'm I'd like to find a way to the sum 'Total Actual HRS PRGM' field value at the parent grouping level and divide it by the sum 'Sold Hours' value at the parent grouping level to get a percent in the '% On Hours Sold' at the parent grouping level.

I don't believe you can use a valueexpression statement when grouping and using the aggregator text mode code.

Any ideas on how to get the value I'm looking for - divide the 'Total Actual HRS PRGM' field value by the 'Sold Hours' value at the grouping level?

Thanks,

Nick

[KatherineLa]

In my instance, this is a column calculation and grouping calculation that produce the correct weighted averages expected. You can use valueexpression in the aggregator portion of your text mode, though it does require some fiddling.

Here's a tutorial with some other examples that sound more like yours: https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/custom-view-samples/gro...

aggregator.displayformat=HTML
aggregator.function=AVG
aggregator.valueexpression=NUMBER(round(WEEKDAYDIFF({entryDate},{My Custom Date Field}),1))
aggregator.valueformat=HTML
description=How many business days between Entry Date and Project Conversion?
displayname=# of Business Days to Project Conversion
querysort=My Custom Date Field
textmode=true
valueexpression=round(WEEKDAYDIFF({entryDate},{My Custom Date Field}),1)
valueformat=HTML

[NickVa1]

Thanks for the response, @KatherineLa .  I'll give this a try.

[NickVa1]

Unfortunately this gives me the mean average.  Similar to applying the "Average" option on the 'Sum this column by' feature.  It adds all of the values within the grouping and divides it by the number of records.

I'd like to take the sum 'Total Actual HRS PRGM' field value at the parent grouping level and divide it by the sum 'Sold Hours' value at the parent grouping level to get a percent in the '% On Hours Sold' at the parent grouping level.