Past Due Tasks in Ranges

I'm trying to find a way to calculate the count of tasks that are "aging." In essence are past due but in tiered ranges. 

I need to know the number of tasks past due in the following ranges:

  • 0-30 days
  • 31-60 days
  • 61-90 days
  • 90+ days

I can calculate the number of tasks that have due days in those overlapping ranges. For example, I can easily calculate the tasks due in the past 30 days. Then, calculate the number past due in the past 60 days. But then I end up with overlapping values, as some of the tasks counted in the "past 30-day" rage are also included in the "past 60-day" rage. 

I'm missing something simple, but I can't seem to nail this down. 

2
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos
6 commentaires

I'm curious about a solution for this as well. I ran into a similar problem when trying to color code custom field values, but if it could be solved on an analytics board that would be great.

1
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hey Brian Gotti! Good to see you in here, it's been a while!
The best way to do this is to set up a widget (most likely a column widget) is to use multiple Values that are filtered to limit the due dates. The formula is a bit intense, but you'll need to combine two IF statements to capture the situation where a task is over X days but under Y days overdue.

For example, for the tasks 31-60 days overdue, you can add some filters:

  • Add a filter on the widget for "Task Status Group" of Active (to filter out Completed tasks)
  • Add a value and go to formula mode.
  • Create a formula that uses the IF() function, which counts a task if it is over 30 days and under 61 days. It will look something like this:
  • SUM([Task ID], IF( DDIFF( NOW([Task Due Date]), [Task Due Date] ) > 30 , IF( DDIFF( NOW([Task Due Date]), [Task Due Date] ) < 61, 1, 0 ), 0 )
  • The "DDIFF" function counts the number of days between two dates. Putting any date within the "NOW" function returns today's date. So, the DDIFF functions should return the difference between today's date and the Due Date.
  • IF statements are structured like this: IF( <True/False Statement>, <Value if True>, <Value if False> ). So, if the DDIFF statement is greater than 30, the "Value if True" then evaluates the second IF statement to determine if it is below 61.
  • If both statements are true, it returns a "1" for the task.
  • Finally, after running through this calculation for each task, the calculation SUMs the 1's returned for every task.
  • You would fill out a separate Value/Formula for each column you're trying to build (e.g. 61-90 days, 90+ days)

I hope this at least gets you started!

Mark

Mark Tapia Community Team at Wrike Wrike Product Manager En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online

Mark Tapia Wrike Team member En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hi Mark Tapia

Good to talk again. 

I'm giving this a go here and getting a syntax error in Analyze. 

And this is the error that it kicks back to me. 

 

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Brian Gotti I believe this is an issue with the output of the DDIFF function. For some reason, we have to put what is called an "aggregator" on it, to turn it from multiple values into a single value. So, in front of your DDIFF function, you'll need to place a MAX function. It will look like this:

...IF( MAX( DDIFF( NOW([Days in Task Due Date])...

Make sure to do this on both DDIFF functions and make sure to close out your MAX function with extra close parentheses after your DDIFF functions.

Mark Tapia Community Team at Wrike Wrike Product Manager En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online

Mark Tapia Wrike Team member En savoir plus sur les fonctionalités et les meilleures pratiques grace à nos Webinaires online

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Thanks Mark Tapia!

Got it to work! 

SUM([Task ID],IF((MAX( DDIFF( NOW([Days in Task Due Date]), [Days in Task Due Date] ))) > 59 , IF(MAX( DDIFF( NOW([Days in Task Due Date]), [Days in Task Due Date] )) < 90, 1, 0 ), 0 ))

Jessie Stith Looks like we got it solved. 

I was able to create a few widgets on a report to display the counts this way:

For the last one, I just did > 180 and < 999.

1
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Thank you Brian Gotti and Mark Tapia!! :D

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Folllowing List for Post: Past Due Tasks in Ranges
[this list is visible for admins and agents only]

Haut de la page
Didn’t find what you were looking for? Write new post