Creating Aging Bucket Based On Days

Hi Alteryx Community,

I would like to create a columns that will identify its aging bucket based on day count.

(Column 2 to 4) For Horizontal (sample 1-15, 16-30, 31-45 and so on...) - This only shows the amount on each bucket. Let say in Column 2-4 with bucket of "1-15", i have amounting to $50 because the day count falls to 10Days.

(Column 1) For Vertical - I have value same as bucket above but it only indicates bucket. Sample, i have Column 0 which calculate the number of day (20Days) then for this Column 1 will have a value of "16-30".

Appreciate your help on this.

-quims

Workflow

Developer

Input

Output

Tips and Tricks

Transformation

Predictive Analysis

Accepted answers

ramesh_neel

@quims - That is pretty much same as what I have shared , its just you have a wider aging bucket , also can you check what the data type of your field [Aging Days] is? it should be in number format (int data type) for it it to work correctly !

TomWelgemoed

Hi @quims ,

Seeing how many buckets you have, it's horrible to have to maintain that in a Formula tool. It's easy to forget a range or a pain if you need to update ranges. If I can suggest 2 alternative approaches:

1. Use the Tile tool. If you select "manual", you can simply enter the upper number for each range and it will group the data in your ranges. Your downside is that it doesn't give the group a name (age bucket), but you can either use a little translation file to do that (my preferred approach) or a formula tool to give a name to each tile. This method saves time and is cleaner

Tile tool.png

2. Use a lookup file. Similar to the tile tool, but you create a file with your age buckets in say, Excel, and specify "Lower" and "Upper". You then need to join this data to your file and assign the appropriate bucket. I think the easiest way is then to use a "Generate Rows" tool to generate the age for every age within your bucket and join that back to your source data. Sounds tricky, but once you wrap your head around it, it's really easy. Also attach an example for you. It think this is actually my favourite method, as it requires little coding and only needs you to specify in a file the upper & lower range and bucket name.

Generate rows 2.png Generate rows.png

Oh, and last thing - @ramesh_neel is of course right that your age needs to be numeric - just use the select tool to force it into Age.

Best,

Tom

Generate rows.yxzp

Tile.yxzp

All comments

ramesh_neel

@quims - Is this what you are after? I am not sure if I understood your question 100% !

Bucket.yxmd

quims

hi @ramesh_neel thank you for your reply but i've been looking differently.

This is what i did after i search with Aging related discussion but again, i didn't get the result i want. i also got an error as "malformed or invalid formula".

in Ageing Bracket column - this column will show the ageing bracket based on ageing days while in 1-15, 16-30 and so on will show the amount.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703