ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
alteryx Community

# Alteryx Designer Discussions

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

## Aging Buckets

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

 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

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

8 - Asteroid

Great that works, thanks very much!

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"

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

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

Labels