Past Due Tasks in Ranges

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.

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 Learn about Wrike’s killer features and best practices with our Online Training Webinars

Mark Tapia Wrike Team member Learn about Wrike’s killer features and best practices with our Online Training Webinars

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. 

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 Learn about Wrike’s killer features and best practices with our Online Training Webinars

Mark Tapia Wrike Team member Learn about Wrike’s killer features and best practices with our Online Training Webinars

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.

Thank you Brian Gotti and Mark Tapia!! :D