Need help with Sub formula in Project Level Report

Question

Hi Friends!

In a project level report, we are trying to create a custom column that portrays the difference between 2 custom fields. The below are the custom fields (their names in bold, sentence below is how the custom fields are calculated):

PIM | Task Cycle Time to In Market Date - Work Min Diff

ROUND(WORKMINUTESDIFF({project}.{program}.{DE:PIM | Program In Market Date},{handoffDate})/480)

PIM | Program SLA Sum

SUM({DE:RED | T-Shirt SLA},{DE:PIM | T-Shirt Deployment},{DE:PIM | Engagement SLA},{DE:PIM | Creative Brief SLA},{DE:PIM | Strategy SLA})

Below was our proposed difference column between these two fields:

displayname=Cycle Time Variance from SLA
valueexpression=SUB({DE:project:program:PIM | Program SLA Sum},{DE:PIM | Task Cycle Time to In Market Date - Work Min Diff})
valueformat=HTML

When this runs, it only recognizes the value that is in the "DE:PIM | Task Cycle Time to In Market Date - Work Min Diff" field. So it feels like something is off with the "DE:project:program:PIM" portion.

Please let me know your thoughts!

skyehansen · Accepted Answer

I can see why you went there, but read the table and example more closely, as it specifically talks about the syntax for field names, not your entire valueexpression. It will be really valuable if you get full understanding from the link, since many admins spend most of their time in crafting valueexpressions and this link is a critical building block to their knowledge.

your valueexpression should break down to something more like this:

1) Any function you use, will follow the syntax shown on the link I provided. In many cases, this looks like "FUNCTIONNAME(operator1, operator2)"

2) Any custom field located on the object you are reporting on, will follow the syntax "{DE:YourFieldName}"

3) Custom fields located on other objects, will follow the syntax "{object}.{DE:YourFieldName}"

A sample syntax would look like FUNCTIONNAME({object}.{object2}.{DE:YourFieldName},{DE:YourFieldName2})

skyehansen · Answer

I think possibly what you need is in the first table of this page, and the short example under the table:

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

i.e. You're working with valueexpressions, so use periods and curly brackets instead of colons and parens.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded