Aging Report for A/R Data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
Alteryx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Very helpful, thank you DiganP!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Has anyone figured a way to insert sum total the aging buckets into a total row at the bottom?
