How to implement AVG in a calculated column in reports?

[Regisschink]

I'm trying to calculate the average of projects that achieved the SLA Target. I managed to transform the SLA Achieved field into 100 (achieved) and 0 (not achieved), but when I try to aggregate this column for an average of all projects, I cannot do this. I would like to use this aggregator to use them in a Gauge graph. Any idea where I went wrong?

displayname=SLA %
linkedname=direct
namekey=SLA Achieved
querysort=DE:SLA Achieved
textmode=true
valueexpression=IF({DE:SLA Achieved}="Yes",100,0)
valueformat=customNumberAsString

aggregator.displayformat=customNumberAsString
aggregator.function=AVG
aggregator.namekey=SLA Achieved
aggregator.valuefield=DE:SLA Achieved
aggregator.valueformat=customNumberAsDouble

[KellieGardner]

Try changing your aggregator.valueformat and aggregator.displayformat to something else like int or val and see if that works.

one thing to note, you can't use text mode columns in the chart reports. You might need to do a calculate field instead of column and have it live on your object.

[Regisschink]

Hi Kellie. Thanks for replying.

Unfortunately it doesn't working. Do you have any idea how can I see an average of SLA on the grouping line?

[KellieGardner]

Is the field setup as a number field on the backend? 

[Regisschink]

It's set to text (Y or N), but I'm converting it to a number (see below the valueexpression). Once this is done, I can see the average SLA achieved for the service (e.g. 94%, etc.).

valueexpression=IF({DE:SLA Achieved}="Yes",100,0)

Do you have any idea whether this possible to be done?

[KellieGardner · Accepted solution]

It can be done. I didn't catch something on your code. The aggregator valuefield also needs to be an expression.

Try this:

aggregator.displayformat=customNumberAsString
aggregator.function=AVG
aggregator.namekey=DE:SLA Achieved
aggregator.valueexpression=IF({DE:SLA Achieved}="Yes",100,0)
aggregator.valueformat=val
displayname=SLA %
textmode=true
valueexpression=IF({DE:SLA Achieved}="Yes",100,0)
valueformat=customNumberAsString

[Regisschink]

Bingo!!!

Thanks Kellie! Great call... one more learning in my journey in Workfront!