Calculation of monthly trend on two dates

[ruma_barman]

Hi All,

I need to create a logic to get the output as monthly trend:

I have two dates (below example) and Contract no (key). Here I need to add count of contracts as and when they get started,monthly similar is the case of document uploaded pending which is substraction of contract started (units) and document uploaded (units)

Suppose:

Contract	Contract Start Date	Document Upload Date
1111	13/01/2021	22/03/2021
1112	30/12/2020	30/12/2020
1114	30/12/2020	25/05/2021
1115	25/01/2021	-
1116	28/01/2021	05/02/2021
1117	15/12/2020	-
1118	15/12/2020	30/12/2020
1119	23/12/2020	-
1120	24/04/2021	-

Output Expected :

MonthYear	#Contracts Started(Units)	#Document Pending (Units)
Dec-20	5	3
Jan-21	8	6
Feb-21	8	5
Mar-21	8	4
Apr-21	9	5
May-21	9	4

Thanks and Regards,

Ruma

[anthonyj · Accepted solution]

Hi,

I've been looking at this problem for the past couple of days trying to work out how to associate the Document Upload Date back to a master date field that is based on the Contract Start Date.
I wasn't able to figure that one out unfortunately but I'm really curious to know if it is possible and how.
In the mean time I was able to remodel your data into a format where you can use the rangesum and above funtions to create the output you're looking for.
This model places the contract numbers next to the MonthYear field where an action has taken place. The flags indicate whether the action was a contract start or Document Upload.

// Create the month columns for the two date fields
data:
Load
*,
monthstart([Contract Start Date]) as [Contract Start Mth],
monthstart([Document Upload Date]) as [Document Upload Mth]
;

load * Inline [
Contract Contract Start Date Document Upload Date
1111 13/01/2021 22/03/2021
1112 30/12/2020 30/12/2020
1114 30/12/2020 25/05/2021
1115 25/01/2021 -
1116 28/01/2021 5/02/2021
1117 15/12/2020 -
1118 15/12/2020 30/12/2020
1119 23/12/2020 -
1120 24/04/2021 -
](delimiter is '\t');

//Create a master table that includes the MonthYear, Contract number and flags against both date fields
Output:
Load
[Contract Start Mth] as MonthYear,
Contract,
1 as [Contract Start]
Resident data;

Concatenate
Load
[Document Upload Mth] as MonthYear,
Contract,
1 as [Contract Doc Upload]
Resident data;

// Fill in the gaps in the months where there's no records and join it back to the contract records
right Join(Output)
Load
monthstart(StartDate, IterNo()-1) as [MonthYear]
while monthstart(StartDate, IterNo()-1) <= EndDate
;
Load
min(MonthYear) as StartDate,
max(MonthYear) as EndDate
Resident Output;

drop Fields [Contract Start Mth], [Document Upload Mth];

In a table use the MonthYear column as dimension and the following as measures:

// Running total of the contracts started
rangesum(above(sum([Contract Start]),0,rowno(total)))

// Running total of the contracts started minus the count that have a document upload date
rangesum(above(sum([Contract Start]),0,rowno(total))) - rangesum(above(sum([Contract Doc Upload]),0,rowno(total)))

I hope this is of some assistance.

Thanks
Anthony

[ruma_barman]

Hi Anthony,

Thank you so much for your time to help me solve the problem.

Thanks and Regards,

Ruma