Expand my Community achievements bar.

# Workfront

SOLVED

## Average values on reports

Level 8

Ok, I'm stuck. I need to create a report that shows me the average number of requests we have received per day this year. Currently I'm grouping by entry date (or the date the request was submitted) I can get a record count in my chart, but not an average. Probably because the date isn't a whole number...? This seems like it should be obvious to me, but I can't get it. Anyone?

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Level 10

Tricky one Samantha,

Assuming that there are 365 days per year (usually), that each person who raises an Issue has an FTE of 1, and that it is the average per day within a particular year you are after (e.g. grouping your report by Entry Date, yearly), mathematically you can think of each Issue raised as being "worth" 1/365, from a SUM perspective (e.g. 365 Issues raised would be 365/365 = 1 per day, on average), in which case, you can effectively achieve the annual average you seek by doing a SUM using this textmode (in an Issue report view column):

aggregator.displayformat=HTML

aggregator.function=SUM

aggregator.namekey=view.relatedcolumn

aggregator.namekeyargkey.0=enteredBy

aggregator.namekeyargkey.1=fte

aggregator.valueexpression=({enteredBy}.{fte}/365)

aggregator.valueformat=HTML

displayname=Average Issue(s) per Day

namekey=view.relatedcolumn

namekeyargkey.0=enteredBy

namekeyargkey.1=fte

querysort=enteredBy:fte

textmode=true

valueexpression=({enteredBy}.{fte}/365)

valueformat=HTML

Regards,

Doug

8 Replies

Level 10

Tricky one Samantha,

Assuming that there are 365 days per year (usually), that each person who raises an Issue has an FTE of 1, and that it is the average per day within a particular year you are after (e.g. grouping your report by Entry Date, yearly), mathematically you can think of each Issue raised as being "worth" 1/365, from a SUM perspective (e.g. 365 Issues raised would be 365/365 = 1 per day, on average), in which case, you can effectively achieve the annual average you seek by doing a SUM using this textmode (in an Issue report view column):

aggregator.displayformat=HTML

aggregator.function=SUM

aggregator.namekey=view.relatedcolumn

aggregator.namekeyargkey.0=enteredBy

aggregator.namekeyargkey.1=fte

aggregator.valueexpression=({enteredBy}.{fte}/365)

aggregator.valueformat=HTML

displayname=Average Issue(s) per Day

namekey=view.relatedcolumn

namekeyargkey.0=enteredBy

namekeyargkey.1=fte

querysort=enteredBy:fte

textmode=true

valueexpression=({enteredBy}.{fte}/365)

valueformat=HTML

Regards,

Doug

Level 10

Sounds very good @Doug Den Hoed‚

I tried the same and got this one. If you refer the attachment, just wondering about How to read this number in which context way?

Best regards,

Kundan

Level 10
Ah; good point Kundan! I’ve edited my previous post to include: displayname=Average Issue(s) per Day I also see, though, that some of your rows have zero in them, implying that they break the FTE = 0 assumption (112/365 = 0.3, where you are seeing 0.2). For accuracy, I suggest you track down and ensure FTE = 1 for all the folks who are entering Issues. ...which, for any purists reading this (we know who we are), is NOT always true (eg part time workers, etc), so if you prefer to add a mandatory custom data parameter at the user level called Average Days Per Year (defaulting it to 365), you could then refer to it in the formula (eg 1/{enteredBy}.{DE:Average Days Per Year}). Same concept, separate view column (and group by month) if you are interested in Average Days Per Quarter, Average Days Per Month, etc. Regards, Doug

Level 8

@Doug Den Hoed‚ This is fantastic. How then could I put this directly into a chart? Is that possible?

Level 10

In order to chart, Workfront needs to "hit" data that is persisted in the database, so since this is calculated on the fly, it isn't eligible.

You could, however, periodically type it in to some custom data, such as a special "Annual Stats" Project, with a Task named and representing each year, with a custom data parameter called Average Issues Per Day, and then chart against those Tasks as a bar chart.

Regards,

Doug

Level 8

Great workaround Doug! The information in an easy to read format is worth that. Thank you.

Level 8

So I've done what you said and I'm a little stuck. I've got a project with the task name representing each year and another custom data parameter called average issues, but I'm struggling with the chart. I can't get anything but a record count on either of the data parameters.

I need my Y-Axis to be the month and the X-Axis to be the average issues. Help?!

Level 10

Hi Samantha,

To get the chart you're after going:

• set the planned start date of each Task to be Jan 1 of the year it represents (noting that you might have to first backdate the start of your Project to the earliest among those, or even earlier, to be safe)