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]
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), "
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.
Solved! Go to Solution.
Views
Replies
Total Likes
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), "
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.
Views
Replies
Total Likes
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), "
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?
Views
Replies
Total Likes
I'm not actually using any calculated fields ... but now I might look into it to help me out in the future!
Views
Replies
Total Likes