Categorizing Maturity Dates in Various Buckets (3 Types of Categories)

[asalgadoponce]

Hi Alteryx experts,

I am struggling on a report that needs to categorize loans in various maturity buckets. I can't figure out a formula that that will categorize the loan in either Week 1 - Week 4, Month 2 - 12 and Over 1 Year. Below is an example of various hypothetical loans with different maturity dates. The desired result is in the 3rd column "Maturity Bucket". The reporting date in the example below is September 30, 2023. 

Loan	Maturity Date	Maturity Bucket
1	10/1/2023	Week 1
2	10/8/2023	Week 2
3	10/15/2023	Week 3
4	10/22/2023	Week 4
5	10/31/2023	Week 4
6	11/6/2023	Month 2
7	12/9/2023	Month 3
8	1/16/2024	Month 4
9	2/19/2024	Month 5
10	3/21/2024	Month 6
11	4/30/2024	Month 7
12	5/1/2024	Month 8
13	6/11/2024	Month 9
14	7/19/2024	Month 10
15	8/24/2024	Month 11
16	9/30/2024	Month 12
17	10/01/2024	Over 1 year

Thank you in advance!

[gawa]

hi @asalgadoponce 

Though I couldn't figure out of the rule you applied to calculate "Maturity Bucket", key is to calculate difference of date.

Please refer to attached workflow how to convert to date format, and to calculate difference. 

[geraldo · Accepted solution]

@asalgadoponce 

Below is an example workflow.
only the 10/31/2023 day does not correspond to the week

[asalgadoponce]

Thanks @geraldo ! Your workflow did the hard part. I think I can figure out a way to group the last of the month in Week 4. I really appreciate it.