Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!
SOLVED

Help with complex valueexpression for late tasks

Avatar

Level 2

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.

1 Accepted Solution

Avatar

Correct answer by
Level 2

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

View solution in original post

4 Replies

Avatar

Level 7

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.

Avatar

Correct answer by
Level 2

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

Avatar

Community Advisor

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/...

Avatar

Level 2

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