This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
@JUSTiiN The first thing you want to do is use the formula tool to calculate the Days past due.
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.