Need Help Figuring Out Complex Loop Logic Formula in Alteryx

Hi Alteryx Community,

As the title suggests, I'm trying to find a way to use some loop logic in an analysis.

I have attached an example and want to work you through the logic.

We have a customer that has multiple loans, some are already closed, some are still active/open.

The goal? to calculate the cumulative amount of time that the customer actually had loans with us - based on a 12month period determination.

For example, if a customer had a loan from 2000 to 2010 and then from 2020 to 2024, then the customer has maintained an actual relationship with us for (10) + (4) = 14 years, not 2024. Simple, right?

However, we need to account for dynamic situations.

For example,

(1) if multiple loans opened in 2000 and are ALL closed, I want alteryx to calculate the longest relationship.

(2) for the 12month period determination, I want alteryx to look for the chronological passage of time, not the successive. So, if loan (A) opened in 2004 and closed in 2010, loan (B) opened in 2004 and closed in 2008, and loan (C) opened in 2012, I need alteryx to look for the chronological difference, meaning to subtract 2010 from 2012 - and not 2008 from 2012. So in this case, the gap is actually 2 years, not 4.

Then the logic continues... from loan (C) to (D)... and on to the next until we have a cumulative count.

Would greatly appreciate it if someone could come up with some logic that can be applied to this situation. Seems like coding would be the solution, but I'm in the hope that Alteryx can also offer the solution.

Thank you very much,

Alteryx Loop Logic Formula.xlsx

Developer Tools

Date Time

Iterative Macro

Accepted answers

All comments

griffinwelsh

I think you can skip the loops entirely and just generate a row for each active time period that you want to consider and then get unique active dates for each customer and count those. I've attached a sample of this that counts the number of days each customer had a loan open. This skips a lot of the steps and intermediate values that you listed, but does yield the cumulative relationship length. You can adjust this to fit the periods that you need. You could also do this more elegantly with an iterative macro and calculations, but this is the easiest solution to build and your record totals shouldn't be crazy high dealing with dates.

Loan time caluclator.yxmd

DanAAC

@griffinwelsh Thank you for the idea, I like it!

But (1) how do we avoid overcounting? if there are multiple loans that opened within 12 months, we just need to count the oldest relationship ship.

Example for the file you attached: loan opened 1999-01-01 and then the next one opened 2000-01-01 (which is within the 12 month time frame), then we'll count the length of the relationship from 1999-01-01 and not 2000-01-01.

How can we address this?

griffinwelsh

@DanAAC The way this works in my solution is that each loan is broken up into into active periods based on its start and end dates using the generate rows tool. I used days, but you could use any period length. Then only unique active periods are retained for each customer using the first summarize tool. You will notice that with my sample data around 2000 rows are dropped at this stage because of overlapping loans for the same customer. Then the final summarize tool gets a count of unique periods for each customer.

DanAAC

Thank you! I see it now - very useful.

When I was trying to run the attached example, I got 26 years. But according to my analysis in Excel it should be

~18 years. The reason being is that the 1st and 2nd loan both opened at the same date, but the 2nd lasted longer (and longer among the first 3 loans) so we only consider it to be the length of the relationship from 1st-3rd loans.

Would you take a look at it please? I am trying to solve this

Alteryx Forum Example.xlsx

DanAAC

@griffinwelsh

Did you get a chance to look at the other example/analysis?

Thank you again!

griffinwelsh

@DanAAC I took a look at this but did not replicate your result. I get ~19.2 years of open loan time with your data set. I get the same answer summing the days of the 3 rows in your excel file as well.

Loan time caluclator.yxmd

Alteryx Forum Example.xlsx

DanAAC

Hi @griffinwelsh ,

I am going to run it a couple more times on different scenarios, but it seems like it can work. I'll provide an update.

Thanks very much!

DanAAC

Hi @griffinwelsh ,

After running a couple more tests, I'm glad to finally say your that solution is really great and super helpful!

Just a small question, but I was wondering if there's a way to slightly modify the calculation of days. It currently counts both the start and end dates instead of from the start to end dates, adding +1 days. So if 07/01/2024 - 07/03/2024 is 2 days, it'll count it as 3 days. Honestly it's not that substantial at all, but I guess it'd be nice to know for good practice.

Thank you very much!

griffinwelsh

@DanAAC that is correct. It counts the closed date as well. You can easily modify the condition expression of the generate rows tool to not count the close day. All you need to do is change the <= to a <. Please mark this thread solved if my solution works for you.

DanAAC

Sounds good.

Before I mark solution, I caught something that I'd like to run by you first. The summarize tool perform count on the dates, not distinct count.

I noticed that some customer relationships (please see attached) ended up being ~500 years old - due to the count formula. I changed it to distinct count and the results seems accurate.

Would you explain why this happened as in some cases the regular count (not distinct) worked as expected? *this is when I run the workflow with all/other relationships, not just one (we have thousands of those).

ID 979 Alteryx Community.xlsx

griffinwelsh

There are two summarize tools. The first outputs a list of all unique customer ids and active loan days. The second is performed on the output of the first and gets the row count for each customer. You could accomplish the same with a single summarize tool using count distinct, but if you have both you should not need to use count distinct as each record output from the first summarize tool is already distinct.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703