Reporting Help: Completed 1 Year Ago Today (Yes/No)

[RobertRe2]

Hi Community -

Is it possible to create a Task report with a column called Completed 1 Year Ago? The column should have 2 values: Yes or No, based on whether or not Actual Completion Date is <= $$TODAY-1y. The column should be available for selection as a Grouping.

If so, how should I do it? Custom Form/Calculated Field? Text mode? Something else?

The problem with my attempts with Custom Form/Calculated Field has been Workfront calculated the calculated field on the day I created it... August 2, 2020, but it hasn't updated it automatically since. So, tasks completed between August 3, 2019 and October 2, 2019 still show Completed 1 Year Ago = No.

Thanks,

Rob

[MoniqueEvans]

Hi Rob,

I was able to do this without any custom calculations. Hopefully this helps.

In the task report I pulled in the column "Actual Completion Date"

On that column I changed the name to "Completed 1 Year Ago" and added 2 conditional formatting rules:

1. If Actual Completion Date is ≤ $$TODAY-1y show text Yes
2. If Actual Completion Date is > $$TODAY-1y show text No

I was not able to cleaning group by Yes / No but instead grouped by year and also sorted by the date which at least puts all of the yes's first.

Hope this helps

[AnthonyIm]

If you'd like a grouping, here is some text mode for that.

group.0.name=

group.0.valueexpression=IF({actualCompletionDate}<=$$TODAY-1y,"Yes","No")

group.0.valueformat=string

textmode=true