Collection in different time periods

[altijani]

Hi Community,

I have a data that shows the total collections for a specific customer, who experienced a reopening of their order after it was closed. I need from that data to obtain the collections for the reporting month. Here is what I have:

ID	Date	Status	Collection date	Amount owed	ever collected
1234	29-Feb-16	Open	.	82.44	0
1234	31-Mar-16	Open	.	82.44	0
1234	30-Apr-16	Open	.	82.44	0
1234	31-May-16	Open	.	82.44	0
1234	30-Jun-16	Open	.	82.44	0
1234	31-Jul-16	Closed	20-Jul-16	82.44	82.44
1234	31-May-18	reopened	20-Jul-16	82.44	82.44

And here is what I need (the last column):

ID	Date	Status	Collection date	Amount owed	ever collected	this month collection
1234	29-Feb-16	Open	.	82.44	0	0
1234	31-Mar-16	Open	.	82.44	0	0
1234	30-Apr-16	Open	.	82.44	0	0
1234	31-May-16	Open	.	82.44	0	0
1234	30-Jun-16	Open	.	82.44	0	0
1234	31-Jul-16	Closed	20-Jul-16	82.44	82.44	82.44
1234	31-May-18	reopened	20-Jul-16	82.44	82.44	0

many thanks,

Altijani

[Reeza]

I suspect this example will not generalize to your actual data. Can you provide a more robust example?

If it does generalize, then you can use something like:

if put(collection_date, yymm6.) = put(date, yymm6.) then this_month = ever_collected;