Ageing method (summary of balance report)

[PassION_es]

Hello,

We have a report of balances with dates.  We want to generate an output summary where dates are converted to the following:

0 to 6 mos.

6 mos. To 1 year

over 1 yr but not over 2 yrs

over 2 yrs but not over 3 yrs

and so on and so forth

Can Alteryx dynamically identify and assign the ageing of each month without relying on the mapping/guide in the attached file?? Assuming the report generated is for the last reporting month (January 2025).  Kindly refer to the dummy input and output.

Thank you in advance.

[davidskaife · Accepted solution]

Hi @PassION_es 

This should work for you:

Couple of points to note -

Ensure you tick Option 6 on the Input Tool (first row contains data) to get the headers in as data

You'll need a 'Reporting Date' field to come in, so it can calculate the dates

Using Dynamic Rename tool to add in the new column names

You can of course rename the headers to whatever you need!

[PassION_es]

Great, thanks! @davidskaife 

[PassION_es]

Dear @davidskaife ,

I have a follow up question.

In this workflow, I will be having multiple input files (Either single or multiple sheets) from different users.  

We will consolidate these files into one and create a complete summary of ageing. 

My question is, i want the workflow to merge all those files or sheets however, the problem is not all dates are similar in each field. 

Example: File 1 has dates 1-31-2024 in field/column 1 while in File 2 it has 7-29-2023 in field 1.  

Is it possible to merge input files with different dates and create the same and a single summary of ageing report?

Please refer to the two attached input files for merging.  Thanks.