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

[Pat Saylor]

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!

[Gaia Paolini]

can you clarify the requirements? If e.g. a task is due in March and completed in February, it is not overdue in January, it is just not completed. Do you need the number of tasks that are ongoing or the number of tasks that are overdue each month?

[Pat Saylor]

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.

[Gaia Paolini]

hi, the screenshot you uploaded says 'restricted'. I suggest to remove it.

[Gaia Paolini]

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'

complete_date_column='Completion_Date'

#change data type to datetime if they are strings

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

  ref_dates_df[reference_date_column]=pd.to_datetime(ref_dates_df[reference_date_column])

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

  data_df[complete_date_column]=pd.to_datetime(data_df[complete_date_column])

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

  data_df[due_date_column]=pd.to_datetime(data_df[due_date_column])

#for each reference date, compute overdue items

overdue=[]

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)]

  overdue.append(overdue_df.shape[0])

   

#add column

ref_dates_output=ref_dates_df.copy()

ref_dates_output['calculated_overdue']=overdue

[Pat Saylor]

This was exactly what I needed and worked perfectly. I am not very experience with Python so the help is very appreciated!