Jump to content

How to plot overdue tasks per month by comparing due date to completion date?

Pat Saylor
Go to solution Solved by Gaia Paolini,

Recommended Posts

I have a connection to an SQL server that provides due date, ID #, and completion date for tasks. I am trying to chart the number of items that were overdue at the end of each month. I also need the overdue items to stay on the chart until it is completed, so if an item is due in January and completed in March, it would show as overdue in both January and February. The problem I'm having is that to chart this, I need to compare the database dates to some reference date column consisting of the last day of the month. I know that it basically needs to be Sum(If([due date] < [reference month] and [completion date] < [reference month],1,0)), but I don't know how to compare the database information with these arbitrary reference dates. Thank you for any help you can provide!

Link to comment
Share on other sites

Apologies, the second "<" sign in my equation should have been ">". Basically if it was due before the end of the month and completed after (or has no completion date) it should be counted for that month. I have attached an Excel file with an example of what I am trying to do.

Link to comment
Share on other sites

  • Solution

From your question, I am not sure whether you want to check one month at a time, or you want to produce a table with all the different reference dates, like you showed in the screenshot.

In both cases, I suggest importing the reference dates table as a separate table, we only need one column (Reference Dates). 

Say we call the table 'Reference Table' and your original table is 'Data'.

A - if you want to check one month at a time:

1 - create a drop down list in a text area, that shows the Unique Values in Column from table Reference Table and column Reference Dates. Assign it to a new string document property called refDate.

2 - create a calculated column in your Data table, called [overdue] as

 ([Completion_Date] is null or [Completion_Date]>=DateTime('${refDate}')) and ([Due Date]<DateTime('${refDate}'))

3 - create a new textarea with a calculated value of Sum(Integer([overdue])) over your Data table.

This will show you the number of overdue tasks for each selected reference date individually. 

B - if you want to check all reference dates in one go:

Here it would probably be best to use a data function. I am showing a possible example of a Python script (note that the indentation is lost in the copy/paste).

Input parameters: data_df is 'Data', ref_dates_df is 'Reference Table'. Both are of type Table.

Output parameters: ref_dates_output is a new reference table with the calculated overdue column.

import numpy as np

import pandas as pd

#allow for different column names

reference_date_column='Reference Dates'

due_date_column='Due Date'


#change data type to datetime if they are strings

if ref_dates_df[reference_date_column].dtype==np.dtype('O'):


if data_df[complete_date_column].dtype==np.dtype('O'):


if data_df[due_date_column].dtype==np.dtype('O'):


#for each reference date, compute overdue items


for refd in ref_dates_df[reference_date_column]:

  overdue_df=data_df.loc[((data_df[complete_date_column].isnull()) | (data_df[complete_date_column]>=refd)) & (data_df[due_date_column]<refd)]



#add column



Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...