How to count overdues day by day

[Skyrone]

Hello everyone,

I'm trying to count the number of invoices with payment delays, day by day. 

I'm trying to get them on a full calendar to say "at this point, I know I have X late invoices, with a total amount of Y", but I cannot find how to perform this.

I looked up for Advanced join with conditions in the join but I think I'm on the wrong track.

For example, let's say I have these 5 invoices :

Table 1

Invoice ID	Amount	Document Date	Payment Date	Payment delay
1	10	01/01/2019	04/03/2019	63
2	20	02/01/2019	05/03/2019	63
3	20	03/01/2019	06/03/2019	63
4	50	03/01/2019	05/03/2019	62
5	100	05/01/2019	08/03/2019	63

A payment is considered delayed if the payment delay is over 60 days.

That means the result I'm trying to get is as follows :

Table 2

Calendar Date	Number of late invoices	Amount
01/03/2019	0	0
02/03/2019	0	0
03/03/2019	1	10
04/03/2019	2	30
05/03/2019	3	90
06/03/2019	2	120
07/03/2019	1	100
08/03/2019	1	100

From my data, I know that, for example, on March 6th, invoices 3 & 5 are still. That means I have 2 invoices late with 120$ in total amount.

Does anyone has any idea of how to get from Table 1 to Table 2 ?

[carlosteixeira]

Hi @Skyrone 

If i understand you need this...

Best regards

[danilang · Accepted solution]

Hi @Skyrone 

Here's one way to do it.

After your invoice list box, generate a list of overdue dates for each invoice.  Sum up the total due on each overdue date as well as the number of invoices due.  Join this to a calendar of the days in question.  Union to fill in the missing dates and clean up, giving you

BTW.  I thinkk you may have made a mistake in your original post.  You showed $120 due on the 6th, but you had 2 invoices paid on the 5th so the actual due was only $20

You also had Interesting use of the TS Filler macro to generate a list of dates.  It works but the Generate rows tool is faster. 

Dan

[Skyrone]

Hi Carlos, Danilang,

Thank you both for your quick answers.

@danilang I just learned a new transformation, "Generate rows" that I never used before ! That fits perfectly what I need.

I initialized CalculationDate with formula "DateTimeAdd([Document Date],61,"days")" instead of 62, because I consider that, for example, if payment delay is 63, that's 3 days late. 

So yes, the result I posted is false because it was more supposed to be this one :

Have a great day,

Alex