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 "&EOMONTH(B4,0),$B$4:$B$20," "&EOMONTH(B4,12),$B$4:$B$20," =1 Year AND "&EOMONTH(B4,60)) == >5 Year Payment Future Obligation Buckets Full DateFixed Payment 1 year and 5 years - Payment04/01/202010 20 100 4005/01/202010 10 100 4006/01/202010 - 100 4007/01/202110 80 20 3008/01/202110 70 20 3009/01/202110 60 20 3010/01/202110 50 20 3011/01/202110 40 20 3012/01/202110 30 20 3001/01/202210 20 20 3002/01/202210 10 20 3003/01/202210 - 20 3004/01/202510 - 40 -05/01/202610 - 30 -06/01/202710 - 20 -07/01/202810 - 10 -08/01/202910 - - -

SumIfs to Create Running Total Based on Date Criteria

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	-	-	-

Workflow

Accepted answers

Blake

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!

kodi.yxmd

All comments

Blake

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 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!

kodi.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703