SKILL EXCHANGE: Continue Discussion on Calculated Field Session

[AnthonyIm]

Hi Everyone ‚Äì my presentation for the Workfront Skill Exchange went live today! There were a lot of calculation examples in the presentation and I wanted to create a space here on the Community to share those so you can easily copy and paste them into your instance. Also, if you attended and didn’t get your question answered (or you didn’t attend and have questions about Calculations), you can post them here. I will try to answer them in a timely fashion

Queue Topic Section:

For Queue Topic – Queue Topic.Name

For Parent Topic Group – Queue Topic.Parent Topic Group.Name

For Parent of Parent Topic Group – Queue Topic.Parent Topic Group.Parent.Name

And here is a reminder of the structure

Extra Calculation: Combining Parent Topic Group with Queue Topic - CONCAT(Queue Topic.Parent Topic Group.Name," - ",Queue Topic.Name)

SLA Section:

To Calculate 2 days from Entry Date (with weekends) – ADDAYS(Entry Date,2)

To Calculate 2 days from Entry Date (without weekends) – ADDWEEKDAYS(Entry Date,2)

Note: If you don’t want to do full days, you need to use ADDDAYS (i.e. ADDDAYS(Entry Date,0.125) for adding 3 hours to the Entry Date)

To capture time stamp when changing to a new status for the first time – IF(Status=‘URV',IF(ISBLANK(Under Review Timestamp),$$NOW,Under Review Timestamp),Under Review Timestamp)

To capture time stamp when an assignee is first added to a request – IF(ISBLANK(Assigned To ID),User First Assigned,IF(ISBLANK(User First Assigned),$$NOW,User First Assigned))

Note: Remember you have to create the field (i.e. Under Review Timestamp) with a blank calculation first, save, and then go back and fill in the calculation.

Did you meet the SLA? – IF(ISBLANK(User First Assigned),"Not Sure",IF(SLA Deadline<User First Assigned,"No","Yes"))

Complex IF Statements Section:

Write out all the IF statements individually and then work from the bottom up replacing the missing part of the above IF statement

My statements –

IF(911 Request="Yes","Urgent",FALSE)

IF(Priority Calculation>30,"High",FALSE)

IF(Priority Calculation<10,"Low","Medium")

What it looks like after moving the last one up level –

IF(911 Request="Yes","Urgent",FALSE)

IF(Priority Calculation>30,"High",IF(Priority Calculation<10,"Low","Medium"))

What it looks like when I repeat –

IF(911 Request="Yes","Urgent",IF(Priority Calculation>30,"High",IF(Priority Calculation<10,"Low","Medium")))

[AndreaDi]

Great session. Thank you!

[NicholeDu]

Is it possible to calculate week-day durations between 2 consistent tasks across multiple projects? Interested to calculate the average completion time (leveraging actual start and actual completion dates) between the same two tasks included on 30+ projects that leverage the same project/task template?

[AnthonyIm]

Hi Nichole - I have done a report like this to get an average on a single task before. I use it in my template audits. If you grouped by the template task names, you'd at least get the two averages and can add them together.

Calculated fields only work on the item they are on OR info on items that they can reference (i.e. You can put Project info in a task calculation)

I know it isn't a calculation, but hope that is helpful

[Heather_Kulbacki]

examples of some things I use calculated fields for

• duration from time an issue is submitted until a particular task on the project is complete
• one field to capture the date/time a task went into a particular status (particularly a pending approval status), then another field to provide the duration between that previous field and when the task was actually completed

[Jason_JB]

We're doing something similar with calculated fields but to track

• condition changes (prior condition, time in current condition, and history of changes)
• history and change of a specific custom field(s) (prior value, difference, date of change, and history of changes)

[Heather_Kulbacki]

Someone asked about Is there a way to account for business days (holidays) with the calculation instead of just ADDWEEKDAYS?

Anthony mentioned using that long schedule ID in your calculation, and potentially needing a nested IF statement if you need to consider multiple schedules.

Someone had suggested to me using "ScheduleID" in place of the ID itself so the calculation would refer to whatever schedule is attached to that project - and it worked!!

example: ADDAYS(Entry Date,2,ScheduleID) on a project or ADDAYS(Entry Date,2,Project.ScheduleID) to make the calculation take the project's schedule into account on a task or issue.

I've needed to do that for WORKDAYDIFF calculations that looked at our instances default schedule, but I needed it to use the project's schedule, but the form was on projects that potentially had different schedules.

[AnthonyIm]

OMG! That is awesome!!! Thank you for sharing, @Heather Kulbacki‚ 😎

[MoniqueEvans]

WOW Heather!! This might just be what I need for a calculated column I have for User PTO. Definitely going to try this and see if it works.

[Krystle_Kibler]

Oooh, I just modified @Heather Kulbacki‚'s ScheduleID tip for a Time Off report and it worked!

Thought I'd share in case anyone else finds it useful. Goodness knows someone else shouldn't have to suffer the time spent figuring it out! 😂

----------------------------------------------------

displayname=Work Days Off

textmode=true

valueexpression=ROUND(DIV(WORKMINUTESDIFF({startDate},{endDate},{user}.{scheduleID}),480),0)

----------------------------------------------------

displayname=Hours

textmode=true

valueexpression=ROUND(DIV(WORKMINUTESDIFF({startDate},{endDate},{user}.{scheduleID}),60),0)

----------------------------------------------------

P.S. The Round function isn't working for me but I left it in there anyway. If anyone knows how to fix, please let me know!

[MoniqueEvans]

Thank you @Heather_Kulbacki & @Krystle_Kibler for this suggestion. I finally updated my PTO report, and it worked like a charm. Now my Days column reflects everyone's personal schedule and not just the US holidays. I have added in an aggregator so that it sums the total number of days in the grouping, this way no one has to count how much time has been scheduled.

aggregator.function=SUM
aggregator.name=Duration
aggregator.valueexpression=CONCAT((ROUND(WORKMINUTESDIFF({endDate},{startDate},{user}.{scheduleID}),2))/480," days")
aggregator.valueformat=html
displayname=Total Time Off (in Days)
linkedname=direct
shortview=true
textmode=true
valueexpression=CONCAT((ROUND(WORKMINUTESDIFF({endDate},{startDate},{user}.{scheduleID}),2))/480," days")
valueformat=HTML​

[johnm80842917]

When making our SLA calculation, we had an issue with SLA weekday calculation. When working in calculated fields specifically using the ADDWEEKDAYS, a user selects Thursday or Friday 7:00 PM EST, Workfront convert the date to Friday 12:00 AM UTC and add 1 weekday (Calculation: ADDWEEKDAYS(custom date field,1)). The result will be Monday 2022 12:00 UTC which then outputs Sunday 7:00 PM EST date. Is there another calculation we can use to get SLA to display Friday 7:00 PM EST or Monday 7:00 PM EST as that would be ideal and I've told by Workfront development/customer support that the field is functioning as designed.

[AnthonyIm]

Hi John - it is very interesting.. if the starting time is part of the work day, it seems to work. But if it isn't, the issue you mentioned does seem to happen

I tried doing something with CLEARTIME, which did change it back to April 18th, but 12:00AM on the 18th so the time is off.

Anyone "Workfront Ninjas" have an idea on this?