Need help creating a calculated field to determine TAT

[DawnSc]

I am trying to create a calculated field in a custom form that will show me the average turn around time form Project Initiation To Approved Go Live date on all completed projects.  I cannot figure out how to get two expressions in the calculated field.  I know one would be DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date})

but I don't know how to add the expression to get the average.  Any suggestions?

[skyehansen]

You cannot create a calculated field in one project form that will search for other projects in the system and then run math to calculate an average over all. I think you're thinking about a report, in which case, you would create a column for your turn time, set it to Average, and then group by that column.

[DawnSc]

I have the Ave TAT calculation in the report however, because it is a text mode field, I cannot group by that column and the report is to provide Ave TAT by project size year over year.  Everything I read states that in order to group in a report, it has to be a field in a custom form.  Any suggestions how I can create a report with Average TAT by project size if I can't group by the column because it is a text mode field?

[skyehansen]

Do your single turn time calculation in your custom form, and do the "averaging" part in the report.

[DawnSc]

Did that but it doesn't work because you can't group on a text mode field.

[skyehansen]

You created a calculated field to replace your text mode column. Group by that calculated field, the single turn time for that project.

[DawnSc]

Tried that but it isn't working.  Thanks for your suggestions.  

[skyehansen]

Submitting a case to Support can sometimes be helpful, as they are always able to gently troubleshoot and guide you into giving as much information as possible. Right now, "isn't working" doesn't give me enough information to help you do anything.

[DawnSc]

I created the calculated field in the form that gives TAT per project.  I created a text mode column in the report that shows the average TAT.  The field I need to group by is the text mode column field and I cannot do that so I tried the other way and grouped by the TAT at the project level and the result is not what I need.  I grouped by Project size, Turn around time and Approved Implementation date (Year).  When I go to the chart, I do not have Turn Around Time as an option to select.  

Grouping is as follows 

[skyehansen]

things to check:

1) In your form, is your calculated field formatted as a number? 

2) in your report, you needed to put it in the column view. When you bring it into a column view, are you selecting Average under the dropdown that says "Summarize this column by" in the columns?

[DawnSc]

Hi,

I just went back and looked at my calculation and it is saved as text.  Is there a way to change the calculation to number now that t has been created?

I do have the column view in my report but I don't have the option to select summarize by average and I assume that is because the calculated field is text and not number.

[skyehansen]

no, just copy the calculation and make a new calculated field and format it as number

[DawnSc]

WOOOHOOOO!!!!  It worked.  THANK YOU!!!!  One last question (hopefully) now that I have the average turn around time by project size in my report chart.  Is there a way and the year in there so it would show ave TAT by project size for each year 2021-2024?  So it looks something like this?

[skyehansen]

I would probably say to look into combination charts? Or work with a remote consultant if you can't get it. 

[quarkmage]

@skyehansen 
Thanks for your input on this question.  I have not reviewed this in a while, and previously was stumped on how to get TATs out of Workfront.  Historically relied on exporting the data and processing outside of WF and I am not well versed in the text mode and programming.
It looks like the solution may be in this string.  
I want to calculate TATs based on dates in WF. I have several custom date fields.
I will try to summarize the approach and would love it if you could confirm or suggest corrections:
1) Create a calculated custom field that will take the difference between two date custom fields.
example of: DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date}) was presented above.  i assume i could substitute in my custom fields.
side question: does DATEDIFF exclude weekends?  i want TAT in working days, which WF may already exclude.
Now i have a custom field that calculates the difference between two dates.
I can then use reporting features, like monthly grouping to do, say monthly averages of this new TAT custom field. then perhaps use a combination chart to show TAT vs total count of objects or other field.

Thanks for the review and continued support to the community.

[skyehansen]

@quarkmage  -- this link will provide you with all the expressions available, as well as the syntax for each. You should find weekdaydiff is probably more suitable.

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

[Sujatha_TS]

hi, 

I do this using actual start and actual completion time - 

field name - Turn Around Time --> formula to be used  --> Round(DATEDIFF({actualCompletionDate},{actualStartDate}),2)

You might want to try using Round(DATEDIFF({DE:Approved Implementation Date},{DE:Project Initiation Date}),2)

I also do the grouping to show these in charts using this formula - 

IF({DE:Turn Around Time}<5,"<5 Day Turnaround",IF({DE:Turn Around Time}<=10,"5-10 Day Turnaround"))) add as much as grouping you might want. 

hoping this helps. 

[quarkmage]

@Sujatha_TS 
Thanks for the additional suggestions.  i am trying to implement some TAT analysis into my current reporting but am not well versed in the programming and text mode.  Are you using the rounding function to create some time buckets? i am not sure how granular a calculation field between two dates gets.

For your TATs are you creating a calculation field formatted as a number, and providing the text-based program in the Instructions field?

Thanks for any discussion and support.