Group milestone tasks by quarter in project report using text mode.

[MattMc2]

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:

[Doug_Den_Hoed__AtAppStore]

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

[MattMc2]

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

[MattMc2]

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

[MattMc2]

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

[Doug_Den_Hoed__AtAppStore]

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

[MattMc2]

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 😄