Need Help Figuring Out Complex Loop Logic Formula in Alteryx

[DanAAC]

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, 

[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).

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