Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Month against previous months on a rolling basis

ramesh_neel
11 - Bolide
11 - Bolide

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 .  

 

 

Alteryx ACE | Sydney Alteryx User Group Lead | SparkED Contributor and Mentor
2 REPLIES 2
DavidP
17 - Castor
17 - Castor

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

ramesh_neel
11 - Bolide
11 - Bolide

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

Alteryx ACE | Sydney Alteryx User Group Lead | SparkED Contributor and Mentor
Labels