How to implement AVG in a calculated column in reports?

Question

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 · Accepted Answer

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?

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

KellieGardner · Answer

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded