Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Month against previous months on a rolling basis

Highlighted
Meteor

My very first alteryx post so please do let me know if am not clear with my question.

 

I have sample loan data in the below format(copied from the attached sheet )

 

Loan_IDMonth_EndLoan_AmtLoan_Bal
L100031/03/20185669256000
L100030/04/20185669255894
L100031/05/20185669255065
L100030/06/20185669254268
L200031/07/20185669253421
L200031/03/2018102349989
L200030/04/2018102349805
L200031/05/2018102349620
L200030/06/2018102349434
L200031/07/2018102349247

 

Against each loan I need to create two new columns as ,

 

previous_month_end

previous_loan_bal 

 

on a rolling basis.

 

so my output should look like,

Loan_IDMonth_EndLoan_AmtLoan_BalPrev_Month_EndPrev_Loan_Bal
L100031/03/2018566925600028/02/2018 
L100030/04/2018566925589430/03/201856000
L100031/05/2018566925506530/04/201855894
L100030/06/2018566925426830/05/201855065
L200031/07/2018566925342130/06/201854268
L200031/03/201810234998928/02/2018 
L200030/04/201810234980530/03/20189989
L200031/05/201810234962030/04/20189805
L200030/06/201810234943430/05/20189620
L200031/07/201810234924730/06/20189434

 

I used a formula to find the previous month end as DateTimeAdd([Month_End],-1,"month"), but am not quite sure as to how to find the loan_bal corresponding to that month .

 

Any help would be highly appreciated :)

 

PS - My data volume is quite enormous with close to 60 columns .  

 

 

Highlighted
Pulsar

Welcome to the Community!

 

Your formula for getting the previous month end won't always work, for instance, 30-Apr minus 1 month = 30 Mar. So, I'd change the formula to:

 

DateTimeAdd(DateTimeAdd(DateTimeAdd([Month_End],1,'day'),-1,'month'),-1,'day')

 

Now you can use a join tool to join month end to previous month end and get your prev balance, like this:

 

prev loan amount.png

Highlighted
Meteor

Thanks heaps for the super quick response ! works like a charm :) 

Labels