Creating Aging Bucket Based On Days

[quims]

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

[ramesh_neel]

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

[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.

[ramesh_neel · Accepted solution]

@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 · Accepted solution]

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

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.

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

[quims]

Thank you @ramesh_neel 

I was able to change the type and working nicely!

-quims

[quims]

Thank you, @TomWelgemoed 

This is very helpful. Aside the original workflow, I'm also following your suggestion to a new set of workflow. Still trial and error but very useful and possible lessen the time while running the workflow.

-quims