SumIfs to Create Running Total Based on Date Criteria

[koshodi]

Hello!

Hope no one attacks me for this, but this may be a case where Excel trumps Alteryx. Hoping someone here can prove me wrong!

I am trying to calculate the future payment obligation based on separate date criteria. For example, if DATE (in column) is < 1 year from DATE include in sum payment amount and then continue this formula down. The example attached is how it is done in Excel, I would like to replicate the same in Alteryx. For additional reference, below are the three Excel expressions used to create the obligation buckets.

1. =SUMIFS($C$4:$C$20,$B$4:$B$20,">"&EOMONTH(B4,0),$B$4:$B$20,"<="&EOMONTH(B4,12))  == <1 Year Payment

2. =SUMIFS($C$4:$C$20,$B$4:$B$20,">"&EOMONTH(B4,12),$B$4:$B$20,"<="&EOMONTH(B4,60))  == >=1 Year AND <5 Year Payment

3. =SUMIFS($C$4:$C$20,$B$4:$B$20,">"&EOMONTH(B4,60))  == >5 Year Payment

		Future Obligation Buckets
Full Date	Fixed Payment	<1 Year - Payment	>1 year and <5 years - Payment	>5 years - Payment
04/01/2020	10	20	100	40
05/01/2020	10	10	100	40
06/01/2020	10	-	100	40
07/01/2021	10	80	20	30
08/01/2021	10	70	20	30
09/01/2021	10	60	20	30
10/01/2021	10	50	20	30
11/01/2021	10	40	20	30
12/01/2021	10	30	20	30
01/01/2022	10	20	20	30
02/01/2022	10	10	20	30
03/01/2022	10	-	20	30
04/01/2025	10	-	40	-
05/01/2026	10	-	30	-
06/01/2027	10	-	20	-
07/01/2028	10	-	10	-
08/01/2029	10	-	-	-

[Blake · Accepted solution]

Hi @koshodi 

I hope you haven't been attacked on the Alteryx community before! Some users are pretty passionate... 

See below and attached for an example of how to build out your process in Alteryx. Solving problems in Alteryx is generally different than in Excel but can be more dynamic and arguably more fun! 

I started by converting your provided dates to a date format and appended all records to each other. I then filtered out any dates in the past or equal to the date being calculated. After that, I determined which bucket a date would fall in and crosstabbed to get the results. One Dynamic rename later, you have your table! 

I created a little lookup table for the names of the buckets. This could have been done differently but I wanted to keep it relatively straight forward. 

I also spit balled the days for less than a year and 1-5 years because Leap Days are weird. 

Hope this works for you, good luck on your Alteryx learning journey!

[koshodi]

Apologies for the late response. But yes, this worked like a dream! Leap days were definitely tricky but for my particular scenario switching from "days" to "months" seemed to work just fine.

Also, in the cross tab tool, I summed the appended payment amount which I renamed to "FUTURE_PAYMENT" instead of the original amount. Not sure if that makes a huge difference but figured I'd let any interested parties from the community know.

Thanks for the help!!

[koshodi]

Hello,

Thanks again for your original response on this. I do have a follow-up question as you had mentioned that this could be accomplished differently.

Although this solution worked great for the example provided and, subsequently, my test data, in actual production (think: 100,000+ rows of data) the append tool takes a significant amount of time (~1 hour) to run and does not seem sustainable as the amount of data will only increase going forward. Would an alternative solution account for the large amount of data?

Thanks!