Help with report - Planned completion date vs Actual completion date

[MartaSo]

Hi, I need a help with building task report pulling data from 2022 showing only tasks that were completed late, meaning that actual completion date was greater than planned completion date. Does anyone have a text more allowing to pull only those tasks in report please? or have any suggestion how to achieve it?  Many thanks! 

[Richard_Le_ · Accepted solution]

Hi,

This can be achieved with the following text mode filter:

actualCompletionDate=FIELD:plannedCompletionDate
actualCompletionDate_Mod=gt

Best Regards,

Rich.

[MartaSo]

Rich thanks so much! that works but also is pulling tasks that are completed on the planned completion date. Can I add somewhere to the code +1 so the actual completion date is truly shows overdue tasks please? 

[Richard_Le_]

The reason for this is likely to be that the task was completed after the planned completion date (with time taken into consideration). For example, if a task was planned to complete at 16:00 on 01/06/23, but was actually completed at 17:00 on 01/06/23, it is classed as late in Workfront.

You've got a couple of options here. You either align the planned completion date of tasks to always be at the end of a business day. Or, you would need to add a calculated field to each of the tasks that measures the difference in days between actual and planned completion, which can then be used in your report filter.

The calculated field would look something like this: DATEDIFF({actualCompletionDate},{plannedCompletionDate})

Make sure that you set the calculated field to be number format so that it can be used correctly in reports.

Best Regards,

Rich.

[MartaSo]

Thank you Rich! That's really helpful. I appreciate your help. 

[Madalyn_Destafney]

Hi Rich! Does this work with actual completion date filters? I wanted to pull in tasks that were completed past the planned completion just from past two months so added the bottom line of text mode here, but it's still pulling all tasks when past planned date, not just from what was actually completed past two months. Thoughts?

actualCompletionDate=FIELD:plannedCompletionDate
actualCompletionDate_Mod=gt
actualCompletionDate_Range=$$TODAYe-2m

[Madalyn_Destafney]

@Richard_Le_ I tried this too but can't crack it:

actualCompletionDate=$$TODAYb-2m
actualCompletionDate_Mod=between
actualCompletionDate_Range=$$TODAYe-2m
actualCompletionDate=FIELD:plannedCompletionDate
actualCompletionDate_Mod=gt

[Richard_Le_]

Hi Madalyn,

You can't apply multiple filter rules against the same parameter, which is why this isn't working. However, what you could do is apply a prompt for actualCompletionDate allowing you to select the date range you want to pull in. And also use the text mode filter to only show tasks that were completed after the plannedCompletionDate.

This should work.

Best Regards,

Rich.

[Madalyn_Destafney]

Hi Rich, I'll try out a prompt. Was hoping to avoid a prompt and instead only have a rolling past couple months show in the report. To press my luck here - I don't care if it's past due a day or so. Is there a parameter we could add to say if it's past due by 3+ days to only show then?

[Richard_Le_]

Hey,

The way to achieve this would be to add a calculated field to all of the tasks that works out the difference in days between planned and actual completion. You could then add a filter rule that looks for a value of 3 or above. 

The calculated field would look something like this: DATEDIFF({actualCompletionDate},{plannedCompletionDate})

Make sure that you set the calculated field to be number format so that it can be used correctly in reports.

Best Regards,

Rich.

[Doug_Den_Hoed__AtAppStore]

To be continued...

Regards,

Doug