Aging Report for A/R Data

JUSTiiN

Hey Community,

I'm needing help one more time!

I'm working with a raw data sample trying to separate the A/R aging report into groups 1-30, 31-60, 91-120, 121-150 and 151-180. Previously, I was able to take the Raw Data (Raw Data attachment below) and create a pivot table showing just the "Days Late" and "Total Amount Due" in the pivot table. Once I did that, I was able to group them doing, "Group > Starting at: 1 | Ending at: 180 | By: 30", and it would generate a break down of the total amount due within each of those ranges (refer to Completed Data attachment; worksheet "pivot table"). I'm essentially trying to yield the same data in the pivot table worksheet and the 60-90 day worksheet, but instead of Excel, using Alteryx. In the raw data I'm using today's date as, 12/31/2021.

When I first attempted this, I did Input Data using my Raw data, then went to the Filtering command, but can't seem to figure out a way to run multiple filters. Again still new to this program, so I could already be messing up from the get-go. If you know how to do this, could you please provide the workflow showing me what steps you're taking so that I can mimic it?

Thanks in advance, JUSTiiN

Raw Data for Aging Report of AR.xlsx

Completed Data in Excel (Showing60-90).xlsx

DiganP

@JUSTiiN The first thing you want to do is use the formula tool to calculate the Days past due.

DateTimeDiff('2021-12-31',[Due Date],'days')

After that, you can create a conditional statement to bucket the days late.

if [Days Past Due]<=30 then '1-30' 
elseif [Days Past Due]>30 and [Days Past Due] <= 60 then '31-60' 
elseif [Days Past Due]>60 and [Days Past Due] <= 90 then '61-90' 
elseif [Days Past Due]>90 and [Days Past Due] <= 120 then '91-120' 
elseif [Days Past Due]>120 and [Days Past Due] <= 150 then '121-150' 
else '151-180' endif

You can then use the summarize tool to do the pivot table. Attached is the workflow.

Is this what you are looking for?

Note: I used 2021-12-31 as the final date to calcualte the days past due. You can change that in the formula tool if you'd like. 

Pivots.yxmd