Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

Aging Buckets

Highlighted
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

Highlighted
Alteryx Certified Partner

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

 

 

 

Highlighted
8 - Asteroid

Great that works, thanks very much!

Highlighted
Alteryx Certified Partner

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

Labels