Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
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