Hello,
I'm a beginner and need help in designing a workflow. Basically I need some sort of aging report based on 2 files. (1) a report of existing clients with their latest transaction date before the current month (eg. March 2020), and (2) transactions for the current month.
Bottom line is I need a report (see result report below) that
(a) Find the latest transaction date for the month (eg. March 15, 2020), this date will be the "End Date" column, and pick all clients and amounts on that date.
(b) Find the latest transaction date before the "End Date" for each client, this will be the new "Start Date" for each client. (If no previous transaction before "End Date", then "Start Date will be same date as "End Date").
(c) Create a column for the number of days between "Start Date" and "End Date".
(File 1) Latest transaction before March 2020
Start Date | Client |
3/15/2019 | Client A |
1/1/2020 | Client B |
2/15/2020 | Client C |
(File 2) March 2020 transactions
March Dates | Client | Amount |
3/1/2020 | Client C | 100 |
3/1/2020 | Client D | 150 |
3/5/2020 | Client C | 200 |
3/15/2020 | Client A | 300 |
3/15/2020 | Client C | 400 |
3/15/2020 | Client D | 500 |
3/15/2020 | Client E | 600 |
Result report
Start Date | End Date | Client | Amount | Days |
3/15/2019 | 3/15/2020 | Client A | 300 | 366 |
3/5/2020 | 3/15/2020 | Client C | 400 | 10 |
3/1/2020 | 3/15/2020 | Client D | 500 | 14 |
3/15/2020 | 3/15/2020 | Client E | 600 | 0 |
Thank you,
Solved! Go to Solution.
Thanks @echuong1, it's not quite the perfect result but I guess you're workflow is on the right track just needs a little tweaking. I'll also try to play around this workflow hopefully find the right tools and formulas.
The only error were on:
Client C - your "Previous Transaction Date" was Feb 15 2020 while it should be Mar 5 2020.
Client D - your "Previous Transaction Date" was Mar 15 2020 while it should be Mar 1 2020.