How to get TAT into a report

[DawnSc]

I am trying to create a report to show the average turn around time for each of our projects (not tasks but whole projects).  We group our projects by project size and we want to know what the average TAT is for each group.  The TAT calculation on the report is a calculated text mode field so I cannot add it to the groupings when creating the report.  Our leadership team has asked for this report and right now, I am exporting it in to excel and creating a pivot chart to provide the requested info.  Is there a way to get this in a Workfront Report?  I do not know nay other way to calculate the turn around time other than the text field I have.

aggregator.displayformat=double
aggregator.function=AVG
aggregator.valueexpression=DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})
aggregator.valueformat=HTML
displayname=TAT
textmode=true
valueexpression=DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})
valueformat=HTML

[KatherineLa]

Can you clarify a little more on how you need the calculation to behave, or what isn't calculating the way you expect based on the above? I have reports that use textmode calculations very similar to your example, and they calculate as expected based on the report's Grouping. 

One thing I know that can make aggregated calculations difficult to work with is if the underlying data isn't actually numeric or dates the way you expect. You might have a field that says 1, 2, 3, 4 - but if it's being stored as a Text value rather than Numeric it causes issues. 

If the line-level calculation you show is working, but the aggregation isn't, I'd try tweaking the aggregator.valueexpression line to read like this to force Workfront to understand that the result of the calculation is numeric. 

aggregator.valueexpression=NUMBER(DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date}))

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/custom-view-samples/gro...

[DawnSc]

The calculation is working as expected.  The problem is I cannot get the calculated TAT field on the report chart as I cannot group by the TAT field.  Without being able to add it to the groupings, I cannot chart it.

[KatherineLa · Accepted solution]

Ahh, I see now. I misread the original question. If your calculation is happening within a report column, there's no trick I'm aware of for being able to group/chart that calculation. If your calculation is on the custom form attached to your project object, then you should be able to group by it - again assuming that the data is being stored as numeric rather than text. 

See here for some other conversations on it: https://experienceleaguecommunities.adobe.com/t5/workfront-questions/need-help-grouping-calculated-f...

[DawnSc]

Thanks for that info.  I have never done a calculated field in a custom form.  If I am trying to determine the average TAT which would be the project initiation date to approved go live date for all projects as an average, would the calculation be the same in a calculated field on the form as it is in the column on the report?

aggregator.displayformat=double
aggregator.function=AVG
aggregator.valueexpression=DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})
aggregator.valueformat=HTML
displayname=TAT
textmode=true
valueexpression=DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})
valueformat=HTML

[Rafal_Bainie]

in a calculated field you will only need this bit:

DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})

[DawnSc]

I received the answer needed.  Thanks

[Sujatha_TS]

Hi, We have applied calculated field in project form under admin area, so its shown on projects for admin to validate too. and the same project field is pulled in the report. 

Round(DATEDIFF({actualCompletionDate},{actualStartDate}),2) when we look actual project turnaround. 

you can edit yours to Round(DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date}),2) 

hoping this helps.