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 IDAmountDocument DatePayment DatePayment delay11001/01/201904/03/20196322002/01/201905/03/20196332003/01/201906/03/20196345003/01/201905/03/201962510005/01/201908/03/201963 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 DateNumber of late invoicesAmount01/03/20190002/03/20190003/03/201911004/03/201923005/03/201939006/03/2019212007/03/2019110008/03/20191100 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 ? Overdues.yxmd

How to count overdues day by day

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 ?

Overdues.yxmd

Date Time

Accepted answers

danilang

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

Overdues v2.yxmd

All comments

carlosteixeira

Hi @Skyrone

If i understand you need this...

Best regards

Overdues.yxmd

danilang

Hi @Skyrone

Here's one way to do it.

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

Overdues v2.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703