Count of variables that is before another variable per unique account number

[AnsoneHuman]

Hi all.

I have the following dataset:

Account_no   Date    Status

A123   15-Mar-16     Canceled

A123   15-Mar-16     Declined

A123   07-Feb-17      Declined

A123   10-Apr-17      Finalized

A134   20-Apr-16      Canceled

A134   04-May-17     Canceled

A134   05-Oct-17      Declined

A134   06-Mar-18     Finalized

I would like to calculate the amount of applications, per account_no , made 1 day before the application,1 week before the application and 1 year before the application ,also the amount of canceled applications 1 day before the application,1 week before the application and 1 year before the application .

I hope this makes sense.

Thank you for your help .

[novinosrin]

HI @AnsoneHuman Can you please post a sample of your expected output for your input sample and a few words explaining the logic in deriving the result you want? Thank you

[AnsoneHuman]

Account_no	Date	Status	Count Same Day	Count 1Day before	Count 1Week before	Count 1Month before	Count 1Year before	Count Cancelled Same Day	Count Cancelled 1Day before	Count Cancelled 1Week before	Count Cancelled 1Month before	Count Cancelled 1Year before
A123	15-Mar-16	Canceled	0	0	0	0	0		0	0	0
A123	15-Mar-16	Declined	1	0	0	0	0	1	0	0	0	0
A123	07-Feb-17	Declined	0	0	0	0	2	0	0	0	0	1
A123	10-Apr-17	Finalized	0	0	0	1	3	0	0	0	0	1
A134	20-Apr-16	Canceled	0	0	0	0	0	0	0	0	0	0
A134	04-May-17	Canceled	0	0	0	0	1	0	0	0	0	1
A134	05-Oct-17	Declined	0	0	0	0	2	0	0	0	0	2
A134	06-Mar-18	Finalized	0	0	0	0	2	0	0	0	0	1

With Same day -I mean on the same day

With 1Day before- I mean in the last day

With 1Month before -I mean in the last month

With 1Year before-I mean in the last year

I hope this is what you are looking for

[AnsoneHuman]

Was anyone able to solve this problem?