Report grouping aggregator % | Community
Skip to main content
N
NickVa7
New Member
July 30, 2024
Question

Report grouping aggregator %

  • July 30, 2024
  • 1 reply
  • 598 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

K
Community Advisor
KatherineLaCommunity Advisor
Community Advisor
July 30, 2024

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/grouping-calculation-between-two-fields-aggregated-in-grouping

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

 

N
NickVa7Author
New Member
July 31, 2024

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

N
NickVa7Author
New Member
July 31, 2024

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.

Terms & ConditionsAccessibility statement

Loading footer...