How to create a report which will list the issues based on last updated status change date?

[anasooya]

We want to create a report in workfront which should only show issues which haven't been actioned in the last 5 days ( here haven't been actioned means, the issue status has not been changed from 'Awaiting Approval' to 'Ready to Delivery' in the last 5 days)

Example: If issue status = Awaiting approval on 27th march 2024 and there has been no status change for 5 days since then, this issue should be listed in the report and flaged. I am facing difficulty finding the most accurate field to pull into filter to show the last status update date? How to do this filter config in standard mode or in text mode if someone can share the syntax please?

[DarcySm1]

There's an "Overdue Tasks not Updated in 1+ Weeks" report in the System Admin Maintenance Dashboard that you might be able to use as a model for what you're looking for. You would need to create an Issue report rather than the Task report, but the filters would be similar.

https://experienceleaguecommunities.adobe.com/t5/workfront-discussions/released-system-admin-mainten...

[NicholeVargas · Accepted solution]

There is a field for Issue > Last Update Date that you could use, however, that can be triggered on a variety of actions, not just a status change, so it may not always be 100% accurate. 

Another way would be to create a calculated field. In this blog, example #1 provides instructions for how to create a calculated field to track dates of status changes, so this could be an option for you (granted, it would only work moving forward vs retroactively populating historical data). By capturing this date in a calculated field, you would be able to use it in filters, views, and groupings.

In your case, it would be something like this which says "If the status is Awaiting Approval (based on the status key, AWA), insert today's date." Depending on the status key associated with the Awaiting Approval status - found in your Setup area - the expression would need to be edited to match. 

Field name = Date of Awaiting Approval

Field expression = IF(ISBLANK(Date of Awaiting Approval),IF(Status="AWA",$$TODAY,Date of Awaiting Approval),Date of Awaiting Approval)

[anasooya]

Thank you so much for providing this solution. It is helpful. We have captured this information in the calculated field and the used in our reports with filter criteria of Date of Awaiting Approval less than $$Today-5. I just wanted to double check if this operator and value is correct in my case. 

[NicholeVargas]

@anasooya When using date-based wildcards, you need to specify the number and the attribute (ex: day (d), month (m), quarter (q) or year(y)) so in your case, it would be $$TODAY-5d. Keep in mind the syntax is case sensitive, so you will need to make sure TODAY is in uppercase letters. Additional information regarding wildcard filter variables can be found here.

[andreadmw]

Hi Nichole! I am in a similar place here but not having success so wondering if you could provide additional guidance.

I found this same post and just tried to use it to calculate when an issue moved to Discovery using the following:

IF(ISBLANK(Discovery),IF(Status="DSC",$$TODAY,Discovery),Discovery)

this gave me an error message: This is an invalid custom expression, please try again.

[NicholeVargas]

@andreadmw When creating calculated fields that references the field in the expression, you have to create a "blank" calculated field first for Discovery and click Apply so the field is created in the backend of the system. Once the field has been created, you can go back in and add the expression. In your case, it would look like the following. Apologies that wasn't clear in the above message - you have to utilize curly braces { } to reference field names in the builder.

IF(ISBLANK({DE:Discovery}),IF({status}="DSC",$$TODAY,{DE:Discovery}),{DE:Discovery})

[andreadmw]

Ah. Thank you. Yes, this is working now.