For each row in a group, compare creation date with not outstanding date for all previous.

Question

Hi.

I am looking at historic invoice data for different customers and I want to calculate for each invoice: “number of previous invoices still outstanding on creation”.

Thus, I have to compare the creation date of an invoice with the column “not outstanding date” (which I have created) for all previous invoices for that customer.

I also have some customers where multiple invoices are created on the same date.

I am assuming I need to create an iterative Macro?

Here is some example data:

CustomerInvoice Numberdate_creatednot_outstanding_datecustomer1103.09.201508.09.2015customer1206.09.201504.10.2015customer1319.09.201509.10.2015customer1424.09.201516.10.2015customer2123.01.201709.02.2017customer2210.02.201702.03.2017customer2314.02.201706.03.2017customer2428.02.201717.03.2017customer2528.02.201717.03.2017

What I want to get:

CustomerInvoice Numberdate_creatednot_outstanding_datenum_outstanding_on_creationcustomer1103.09.201508.09.20150customer1206.09.201504.10.20151customer1319.09.201509.10.20151customer1424.09.201516.10.20152customer2123.01.201709.02.20170customer2210.02.201702.03.20170customer2314.02.201706.03.20171customer2428.02.201717.03.20172customer2528.02.201717.03.20172

jdunkerley79 · Accepted Answer

The easiest way I can think of is to join the data to itself on the Customer field

You can then filter where Invoice numbers are equal and the date_created being between the range of the second invoice

After that summarise to get the count. This will exclude ones with 0 matches so you need to add those back in.

Sample attached