Pat Saylor Posted August 3, 2022 Share Posted August 3, 2022 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 More sharing options...
Gaia Paolini Posted August 4, 2022 Share Posted August 4, 2022 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? Link to comment Share on other sites More sharing options...
Pat Saylor Posted August 4, 2022 Author Share Posted August 4, 2022 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 More sharing options...
Gaia Paolini Posted August 5, 2022 Share Posted August 5, 2022 hi, the screenshot you uploaded says 'restricted'. I suggest to remove it. Link to comment Share on other sites More sharing options...
Solution Gaia Paolini Posted August 5, 2022 Solution Share Posted August 5, 2022 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 npimport pandas as pd#allow for different column namesreference_date_column='Reference Dates'due_date_column='Due Date'complete_date_column='Completion_Date'#change data type to datetime if they are stringsif 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 itemsoverdue=[]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 columnref_dates_output=ref_dates_df.copy()ref_dates_output['calculated_overdue']=overdue Link to comment Share on other sites More sharing options...
Pat Saylor Posted August 8, 2022 Author Share Posted August 8, 2022 This was exactly what I needed and worked perfectly. I am not very experience with Python so the help is very appreciated! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now