Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Anthony,
Thank you so much for your time to help me solve the problem.
Thanks and Regards,
Ruma