Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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