Help with complex valueexpression for late tasks | Community
Skip to main content
J
Jcosta16
Level 2
April 12, 2024
Solved

Help with complex valueexpression for late tasks

  • April 12, 2024
  • 2 replies
  • 711 views

In a project collections report, I'm trying to work through this text mode to show:

If milestone = Complete, CONCAT "Completed, actualCompletionDate" (that part is working)

If milestone isn't complete, CONCAT "Due, plannedCompletionDate" and if the planned completion date is past $$TODAY, include [RED DOT]🔴 🔴 . If planned completion date < $$TODAY, include 🟢.

 

This value expression is successfully showing tasks that are complete or that are not complete but not overdue: valueexpression=IF({milestone}.{name}="STRATEGY APPROVED",IF(AND({status}="DED", {plannedCompletionDate}<$$TODAY), "🔴 [RED DOT]🔴 N/A",IF(AND({status}="DED", {plannedCompletionDate}>$$TODAY), "🟢 N/A",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT("🔴 [RED DOT]🔴 ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

 

Any ideas on where I'm going wrong that I'm not pulling in tasks that are NOT complete and ARE overdue (and including that [RED DOT])? FYI that for some reason the red dot isn't populating here so i've put RED DOT in its place.

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

I figured it out! This will pull in the milestone planned completion date if it's not complete and do a green dot if it's not overdue and a red dot if it is. If it's complete, it'll pull in Completed and then the actual completion date!

 

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED","N/A", {plannedCompletionDate}<$$TODAY), "🔴 ",IF(AND({status}="DED","N/A", {plannedCompletionDate}>$$TODAY), "🟢 ",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT("🔴 ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

2 replies

KristenS_WF
KristenS_WF
Level 7
April 12, 2024

Instead of using {plannedCompletionDate}<$$TODAY, maybe try something like DATEDIFF({plannedCompletionDate}, $$TODAY)<0?

You might try testing it in a smaller valueexpression to see if it gives you what you expect before introducing it into your larger valueexpression.

J
Jcosta16AuthorAccepted solution
Level 2
April 15, 2024

I figured it out! This will pull in the milestone planned completion date if it's not complete and do a green dot if it's not overdue and a red dot if it is. If it's complete, it'll pull in Completed and then the actual completion date!

 

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED","N/A", {plannedCompletionDate}<$$TODAY), "🔴 ",IF(AND({status}="DED","N/A", {plannedCompletionDate}>$$TODAY), "🟢 ",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT("🔴 ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

    skyehansen
    Community Advisor and Adobe Champion
    skyehansenCommunity Advisor and Adobe Champion
    April 13, 2024

    I usually go to the calculated data expressions page to get all my functions and syntax. I'm not seeing anything for your "AND" command, so can you walk me through what it's supposed to do, or provide a link so I can read up on it?

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

    J
    Jcosta16Author
    Level 2
    April 15, 2024

    I'm not actually using any calculated fields ... but now I might look into it to help me out in the future!

    Terms & ConditionsAccessibility statement

    Loading footer...