Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Aging Buckets

collinjd
8 - Asteroid

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

 

DateTodayAgeBrackets
03/01/201802/10/2018272>180D
04/01/201802/10/2018271>180D
05/01/201802/10/2018270>180D
04/05/201802/10/201815190-180D
09/01/201802/10/2018266>180D
10/01/201802/10/2018265>180D
28/08/201802/10/20183531-60D
27/09/201802/10/201856-15D

 

Thanks

Jodi

5 REPLIES 5
KOBoyle
11 - Bolide

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

 

Aging Buckets 2018-10-02.png

 

 

 

collinjd
8 - Asteroid

Great that works, thanks very much!

KOBoyle
11 - Bolide

@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
5 - Atom

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 days30 - 60 days60 - 120 days
Jan-2012168129
Feb-2017024274
Mar-2016327981
Apr-20297194200
May-2015887213
Jun-2015617092
BD
5 - Atom

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

 

 

Labels