Hi All,
Currently, I am working on an AR Aging Analysis Report, my objective is to calculate the outstanding amount along with the age bucket.
For example, assuming the system has data from 2018-02-01 to 2018-04-31, if I select 2018-04-31 as the max date the system should calculate all the invoices from 2018-02-01 to 2018-04-31 and display the outstanding amount along with its aging period (30 Days, 45 Days, etc).
If the Invoice Date is 2018-02-20 then the difference between the max date is 60+ Days, the invoice dates will vary.
| Sample Data |
| Max Date | 4/31/2020 |
| Customer | Invoice No | Invoice Date | Amount | Aging Period |
| A | 1000 | 2/10/2020 | $ 500.00 | 82 |
| A | 1001 | 2/11/2020 | $ 1,000.00 | 81 |
| A | 1002 | 2/12/2020 | $ 250.00 | 80 |
| A | 1003 | 2/13/2020 | $ 750.00 | 79 |
| A | 1004 | 2/14/2020 | $ 350.00 | 78 |
| B | 1005 | 3/14/2020 | $ 450.00 | 47 |
| B | 1006 | 3/15/2020 | $ 500.00 | 46 |
| B | 1007 | 3/16/2020 | $ 650.00 | 45 |
| | | | | |
| Expected Output |
| Customer | Total OutStanding | Aging Bucket |
| <= 45 Days | >45 <=60 | > 60 |
| A | $ 2,850.00 | - | - | $ 2,850.00 |
| B | $ 1,600.00 | $ 650.00 | $ 950.00 | - |
Appreciate guidance and support on the above.
