Group milestone tasks by quarter in project report using text mode. | Community
Skip to main content
M
MattMc2
Level 5
November 30, 2021
Question

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

  • November 30, 2021
  • 2 replies
  • 1240 views

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:

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

2 replies

Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity Advisor
Community Advisor
November 30, 2021

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

M
MattMc2Author
Level 5
December 1, 2021

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

M
MattMc2Author
Level 5
December 1, 2021

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

Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity Advisor
Community Advisor
December 1, 2021

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

    M
    MattMc2Author
    Level 5
    December 1, 2021

    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 😄

    Terms & ConditionsAccessibility statement

    Loading footer...