I need help creating a table containing historical values for Account Receivable data.
I have a table with customer numbers, invoice numbers, doc date, due date and clearing date and invoice amount. My goal is to show a historical graph showing how much debt each customer has. I'm finding it hard to get a table in the way I need in order to plot that data.
This is an example data:
Invoices:
LOAD * INLINE [
Cust, Inv No, Doc Date, Due Date, Clearing Data, Amount
A, 111, 27/10/2015, 26/11/2015, 30/11/2015, 100
A, 112, 03/11/2015, 02/12/2015, 03/12/2015, 200
A, 113, 18/12/2015, 17/01/2016, , 50
B, 222, 28/10/2015, 27/11/2015, 25/11/2015, 500
B, 223, 03/11/2015, 02/12/2015, 04/12/2015, 300
C, 330, 01/12/2015, 31/12/2015, 18/01/2016, 20
C, 331, 04/12/2015, 03/01/2016, , 1000
C, 332, 04/12/2015, 03/01/2016, , 700
];
I need to plot my data by weeks on the x axis of my graph. I need to work out what week the invoice first became open and then closed. I predict I need to get a table looking like:
| Cust | Week | Not Due | Past Due |
| A | 32 | 300 | |
| A | 33 | 300 | |
| A | 34 | 300 | |
| A | 35 | 300 | |
| A | 36 | 200 | 100 |
| A | 39 | 50 | |
| A | 40 | 50 | |
| A | 41 | 50 | |
| A | 42 | 50 | |
| A | 43 | 50 | |
| A | 44 | 50 | |
| A | 45 | 50 | |
| A | 46 | 50 | |
| A | 47 | 50 | |
| B | 32 | 500 | |
| B | 33 | 800 | |
| B | 34 | 800 | |
| B | 35 | 800 | |
| C | 37 | 1700 | |
| C | 38 | 1700 | |
| C | 39 | 1700 | |
| C | 40 | 1700 | |
| C | 41 | | 1700 |
| C | 42 | | 1700 |
| C | 43 | | 1700 |
| C | 44 | | 1700 |
| C | 45 | | 1700 |
| C | 46 | | 1700 |
| C | 47 | | 1700 |
I should then be able to plot my customers against weeks on a stacked bar chart plotting Not Due and Past Due.
I have thousands and thousands of rows of data. I've read that the Peek function could work but that does take a long time to go through each instance. With millions of rows, that is not an option. I've also seen the bitmap option but I never used it so can't get my head round it.
I work in the fiscal calendar and the current week is week 47 hence why my data only goes up to there. I run the report on Monday's so any invoice that is created on Thursday will not be picked up until the following week. Same with cleared invoices. It will drop of the following week.
Any ideas at all?
Thanks!
