SKILL EXCHANGE: Continue Discussion on Calculated Field Session | Community
Skip to main content
imgrund
Adobe Employee
imgrundAdobe Employee
Adobe Employee
April 13, 2022
Question

SKILL EXCHANGE: Continue Discussion on Calculated Field Session

  • April 13, 2022
  • 5 replies
  • 2202 views

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")))

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    5 replies

    A
    AndreaDi
    Level 3
    April 13, 2022

    Great session. Thank you!

      N
      NicholeDu
      Level 2
      April 13, 2022

      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?

      imgrund
      Adobe Employee
      imgrundAdobe EmployeeAuthor
      Adobe Employee
      April 13, 2022

      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
      Community Advisor
      Heather_KulbackiCommunity Advisor
      Community Advisor
      April 13, 2022

      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
        Jason_JB
        Level 5
        April 27, 2022

        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
        Community Advisor
        Heather_KulbackiCommunity Advisor
        Community Advisor
        April 13, 2022

        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.

          imgrund
          Adobe Employee
          imgrundAdobe EmployeeAuthor
          Adobe Employee
          April 13, 2022

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

            johnm80842917
            johnm80842917
            April 13, 2022

            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.

            imgrund
            Adobe Employee
            imgrundAdobe EmployeeAuthor
            Adobe Employee
            April 13, 2022

            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?

            Terms & ConditionsAccessibility statement

            Loading footer...