Aging Buckets

[collinjd]

Hi There

I am trying to create Age and Aging brackets for a large data set. I have seen a couple of replies on here on other posts however the attachment is blocked for me to open. Can anyone assist with how I would create the below formula that I currently use in excel into an Alteryx query. Any help greatly appreciated.

Age 

Date - Today

Brackets

=IF(CN2<5,"<5D",IF(CN2<15,"6-15D",IF(CN2<30,"16-30D",IF(CN2<60,"31-60D",IF(CN2<90,"61-90D",IF(CN2<181,"90-180D",">180D"))))))

Date	Today	Age	Brackets
03/01/2018	02/10/2018	272	>180D
04/01/2018	02/10/2018	271	>180D
05/01/2018	02/10/2018	270	>180D
04/05/2018	02/10/2018	151	90-180D
09/01/2018	02/10/2018	266	>180D
10/01/2018	02/10/2018	265	>180D
28/08/2018	02/10/2018	35	31-60D
27/09/2018	02/10/2018	5	6-15D

Thanks

Jodi

[KOBoyle · Accepted solution]

The following formula should work using the Preparation:Formula tool. Create 'Brackets' as a new field, and set the data type to one of the string types.

IF [Age] < 5
THEN "<5D"
ELSEIF [Age] < 15
THEN "6-15D"
ELSEIF [Age] < 30
THEN "16-30D"
ELSEIF [Age] < 60
THEN "31-60D"
ELSEIF [Age] < 90
THEN "61-90D"
ELSEIF [Age] < 180
THEN "91-180D"
ELSE ">180D"
ENDIF

[collinjd]

Great that works, thanks very much!

[KOBoyle · Accepted solution]

@collinjdSure, no problem. One thing that I noticed in your sample data is that age 5 is listed in the 6-10D bucket. You might want to modify the bucket labels or tweak the formula.

IF [Age] < 6
THEN "<6D"
ELSEIF [Age] < 15
THEN "6-15D"

or

IF [Age] < 5
THEN "<5D"
ELSEIF [Age] < 15
THEN "5-15D"

[ckgregory]

I have an extension of this problem. I am fine with adding aging buckets for the data in its current state based on current date, but what I need to do is generate a report that shows historic aging data. Output to look something like the below table. I am struggling to work out a way to do this dynamically. 

	0 - 30 days	30 - 60 days	60 - 120 days
Jan-20	121	68	129
Feb-20	170	242	74
Mar-20	163	279	81
Apr-20	297	194	200
May-20	158	87	213
Jun-20	156	170	92

[BD]

@ckgregory  you might need to use the Summarize and then Transpose operators,