Hi Workfront gurus,
I have a text mode report that pulls in milestones (zero duration tasks) and groups them according to planned completion date. I can do this using relative dates (greater than, less than today) but I want to group for specific quarters (absolute dates). For example, I'd like to group by Q1 2022, Q2 2022 etc but I can't figure out how to do it.
This is what I have for the relative dates. Any help or guidance on how to change this for specific date ranges would be greatly appreciated.
Incomplete Milestones (tasks) less than 30 days out
column.22.displayname=Column
column.22.sharecol=true
column.22.textmode=true
column.22.value=<div style="padding:5px 5px 5px 5px;border-bottom:2px; background:#cccccc; text-align:center; font-weight:bold; font-size:11pt;border-radius:0px 0px 0px 0px; box-shadow: 0px 0px #858585;"><b>KEY MILESTONES</b></div><div style="padding:3px 3px 3px 3px; border-bottom:0px; background:#f5f5f5; text-align:left; font-size:10pt; border-radius:0px 0px 0px 0px; box-shadow: 0px 0px #858585;"><b>Incomplete (< 30 days out)</b><br>
column.22.valueformat=HTML
column.23.displayname=Column
column.23.listdelimiter=<p>
column.23.listmethod=nested(tasks).lists
column.23.sharecol=true
column.23.textmode=true
column.23.type=iterate
column.23.valueexpression=IF({plannedCompletionDate}<$$TODAYed+30d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}<100,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219>[Green] </font></b>',IF({condition}=1,'<b><font color=e19503>[Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519>[Red] </font></b>',""))),{name}," (Planned: ",{plannedCompletionDate},")"))))))
column.23.valueformat=HTML
Incomplete Milestones (tasks) more than 30 days out
column.24.displayname=Column
column.24.sharecol=true
column.24.textmode=true
column.24.value=<br><b>Incomplete (> 30 days out)</b><br>
column.24.valueformat=HTML
column.25.displayname=Column
column.25.listdelimiter=<p>
column.25.listmethod=nested(tasks).lists
column.25.sharecol=true
column.25.textmode=true
column.25.type=iterate
column.25.valueexpression=IF({plannedCompletionDate}>$$TODAYed+30d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}<100,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219>[Green] </font></b>',IF({condition}=1,'<b><font color=e19503>[Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519>[Red] </font></b>',""))),{name}," (Planned: ",{plannedCompletionDate},")"))))))
column.25.valueformat=HTML
Milestones (tasks) Completed in the past 2 weeks
column.26.displayname=Column
column.26.sharecol=true
column.26.textmode=true
column.26.value=<br><b>Complete (Past 2 weeks)</b><br>
column.26.valueformat=HTML
column.27.displayname=Column
column.27.listdelimiter=<p>
column.27.listmethod=nested(tasks).lists
column.27.sharecol=true
column.27.textmode=true
column.27.type=iterate
column.27.valueexpression=IF({actualCompletionDate}>$$TODAYed-14d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}=100,(CONCAT(" ‚óÜ ",{name}," (Actual: ",{actualCompletionDate}," | Planned: ",{plannedCompletionDate},")"))))))
column.27.valueformat=HTML
Milestones (tasks) Completed more than 2 weeks ago
column.28.displayname=Column
column.28.sharecol=true
column.28.textmode=true
column.28.value=<br><b>Complete (> 2 weeks)</b><br>
column.28.valueformat=HTML
column.29.displayname=Complete (past 30days)
column.29.listdelimiter=<p>
column.29.listmethod=nested(tasks).lists
column.29.textmode=true
column.29.type=iterate
column.29.valueexpression=IF({actualCompletionDate}<$$TODAYed-14d,IF({numberOfChildren}=0,IF({duration}=0,IF({percentComplete}=100,(CONCAT(" ‚óÜ ",{name}," (Actual: ",{actualCompletionDate}," | Planned: ",{plannedCompletionDate},")"))))))
column.29.valueformat=HTML
Sample of current report:
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
Pretty slick so far, Matt!
Although such iterates do not support either sorting or aggregating, I suspect that "more of the same" will get you there, if you break your More Than 30 Days Out into Q1 2022, Q2 2023, etc. to show only those plannedCompletionDates between those absolute date ranges...but (admittedly) recognizing that you'll have to build out as many such ranges as you need (I'd suggest the current quarter, plus three, then a bucket for "Long Range"), and update that formula each quarter to drop one, add one, and change the Long Range cutoff.
Regards,
Doug
Views
Replies
Total Likes
Thanks, Doug! I was afraid of that. I went ahead and built it out and have one minor issue. If a date falls on the last day of the quarter it is excluded. I suspect it has to do with the "less than end of quarter" rule. I am not sure how to do "less than or equal to" instead of just "less than".
e.g.
I have: IF({plannedCompletionDate}<$$TODAYeq+2q
I think need: IF({plannedCompletionDate}<=$$TODAYeq+2q
Any suggestions on how to do this?
column.5.displayname=Key Milestones (Grouped)
column.5.sharecol=true
column.5.textmode=true
column.5.value=<b>This Quarter (Q+0)</b><br>
column.5.valueformat=HTML
column.6.displayname=This Quarter
column.6.listdelimiter=<p>
column.6.listmethod=nested(tasks).lists
column.6.sharecol=true
column.6.textmode=true
column.6.type=iterate
column.6.valueexpression=IF({plannedCompletionDate}>$$TODAYbq,IF({plannedCompletionDate}<$$TODAYeq,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',"")))), {name}," (Planned: ",{plannedCompletionDate},")")))))
column.6.valueformat=HTML
column.7.sharecol=true
column.7.textmode=true
column.7.value=<br><b>Next Quarter (Q+1)</b><br>
column.7.valueformat=HTML
column.8.displayname=Next Quarter (Q+1)
column.8.listdelimiter=<p>
column.8.listmethod=nested(tasks).lists
column.8.sharecol=true
column.8.textmode=true
column.8.type=iterate
column.8.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+1q,IF({plannedCompletionDate}<=$$TODAYeq+1q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")"))))))
column.8.valueformat=HTML
column.9.sharecol=true
column.9.textmode=true
column.9.value=<br><b>Future Quarter (Q+2)</b><br>
column.9.valueformat=HTML
column.10.displayname=Following Quarter (Q+2)
column.10.listdelimiter=<p>
column.10.listmethod=nested(tasks).lists
column.10.sharecol=true
column.10.textmode=true
column.10.type=iterate
column.10.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+2q,IF({plannedCompletionDate}<$$TODAYeq+2q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219>[Green] </font></b>',IF({condition}=1,'<b><font color=e19503>[Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519>[Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")"))))))
column.10.valueformat=HTML
...Repeat...
Views
Replies
Total Likes
Ok, I think I figured out.
Use less than the beginning of the following quarter for the end range
column.8.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+1q,IF({plannedCompletionDate}<=$$TODAYbq+2q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")"))))))
instead of less than the end of the quarter
column.8.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+1q,IF({plannedCompletionDate}<=$$TODAYeq+1q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT(" ‚óá ",IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")"))))))
Views
Replies
Total Likes
For the benefit of anyone who might be following along and/or interested:
I adjusted the formula slightly to remove wasted white space. The original formula resulted in "blank" lines where there were no milestones. I removed the title "column" and added a space after the entry that only comes into play if there is a line added.
column.4.sharecol=true
column.4.textmode=true
column.4.value= [removed this title line]
column.4.valueformat=HTML
column.5.displayname=Next Quarter (Q+1)
column.5.listdelimiter=<div>
column.5.listmethod=nested(tasks).lists
column.5.sharecol=true
column.5.textmode=true
column.5.type=iterate
column.5.valueexpression=IF({plannedCompletionDate}>$$TODAYbq+1q,IF({plannedCompletionDate}<=$$TODAYbq+2q,IF({numberOfChildren}=0,IF({duration}=0,(CONCAT('<b>Q+1: </b>',IF({condition}=0,'<b><font color=03a219> [Green] </font></b>',IF({condition}=1,'<b><font color=e19503> [Yellow] </font></b>',IF({condition}=2,'<b><font color=d30519> [Red] </font></b>',""))), {name}," (Planned: ",{plannedCompletionDate},")",'<br>'))))))
column.5.valueformat=HTML
Views
Replies
Total Likes
You're a brave soul, Matt, and I admire your tenacity.
In addition to your own clever solution, you might also have tried <=$$TODAYeq+1q+1d to catch that last full day (yes, I too was surprised that such time addition can be "stacked" when I first tried it), and/or wrap the very handy CLEARTIME function around your dates, which (in so doing) takes the time component out of the equation...literally and figuratively.
Whichever path you choose, enjoy the view! 🗻
Regards,
Doug
Thanks, Doug! I didn't know about cleartime. That [stacked time addition] looks really handy! I wish I'd known about that sooner It would have saved me a lot of headaches on this and other reports 😄
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies