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")))
Topics help categorize Community content and increase your ability to discover relevant content.
Great session. Thank you!
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?
Views
Replies
Total Likes
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
Views
Replies
Total Likes
examples of some things I use calculated fields for
We're doing something similar with calculated fields but to track
Views
Replies
Total Likes
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.
OMG! That is awesome!!! Thank you for sharing, @Heather Kulbacki‚ 😎
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.
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!
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
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.
Views
Replies
Total Likes
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?
Views
Replies
Total Likes
Views
Like
Replies
Views
Likes
Replies