Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Aging Report for A/R Data

JUSTiiN
6 - Meteoroid

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

3 REPLIES 3
DiganP
Alteryx Alumni (Retired)

@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?

alteryx_logo.png

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. 

Digan
Alteryx
w_chivers
7 - Meteor

Very helpful, thank you DiganP!

Qrobinson
5 - Atom

Has anyone figured a way to insert sum total the aging buckets into a total row at the bottom?

Labels