Artificially created line with dates and values

Question

Hi all,

I have read a lot of the discussions here and I am really struggling to find a solution for the following case:

I have the following dataset:

Period_Start_DateContract_NumberInvoice_NumberInvoice_DateAmount 1Amount 2Amount 3Amount 42021-02-01123456782021-01-1877777222102222021-03-01123456782021-01-1877777112042021-04-01123456782021-01-18777772224052021-05-01777788882021-03-15125124445012021-06-01777788882021-03-1512512555606

Each Contract has it's contract number and invoice number, 1 contract may have more than 1 invoice number.

I would like my dataset to always start from the invoice_date date, as such I need to create an extra line at the top of each contract / invoice number so I can have the following:

Period_Start_DateContract_NumberInvoice_NumberInvoice_DateAmount 1Amount 2Amount 3Amount 42021-01-18123456782021-01-187777700777772021-02-01123456782021-01-1877777222102222021-03-01123456782021-01-1877777112042021-04-01123456782021-01-18777772224052021-03-15777788882021-03-151251200125122021-05-01777788882021-03-15125124445012021-06-01777788882021-03-1512512555606

I will add some colors for clarity:

Period_Start_Date = First Invoice Date  (marked in yellow)

Contract_Number = Same as the first line of the contract/invoice  (marked in orange)

Invoice_Number = Same as the first line of the contract/invoice  (marked in orange)

Invoice_Date = Same as the first line of the contract/invoice  (marked in yellow)

Amount 1 = Same as the first line of the contract/invoice (marked in blue)

Amount 2 = Always 0 for the first line (the created line)

Amount 3 = Always 0 for the first line (the created line)

Amount 4 = Amount 1

Can anyone suggest anything here?

OllieClarke · Answer

Hey @angel0v89

Does this work:

I wasn't sure how the Amount 1 value was supposed to work for those middle records. so I took the last one out of the summarise.

angel0v90.yxmd

angel0v89 · Answer

Hi there,

This works very well!, thank you very much! However, it seems that I have got some outliers, where the date is in the middle of the invoice period. For such cases, I would like to add all values in Amount 2 to Amount 3 before this date. Example:

Period_Start_DateContract_NumberInvoice_NumberInvoice_DateAmount 1Amount 2Amount 301/04/202179973567823/09/20217,12939.61  01/05/202179973567823/09/20217,129594.09  01/06/202179973567823/09/20217,129594.09  01/07/202179973567823/09/20217,129594.09  01/08/202179973567823/09/20217,129594.09  01/09/202179973567823/09/20217,129594.09  23/09/202179973567823/09/20217,12903010.06Artificial record01/10/202179973567823/09/20217,129594.09594.09 01/11/202179973567823/09/20217,129594.09594.09 01/12/202179973567823/09/20217,129594.09594.09 01/01/202279973567823/09/20217,129594.09594.09 01/02/202279973567823/09/20217,129594.09594.09 01/03/202279973567823/09/20217,129594.09594.09 01/04/202279973567823/09/20217,129554.4554.4

Any suggestion or help? Obviously, I can't do a group by because I will break all other records... somehow I need to find the match and then sum the previous period and discard the other records.

OllieClarke · Answer

Hey @angel0v89

How's this:

Using the group by feature in the sample tool means we can focus on the first row per invoice number

edit: updated the formula tool to fix the period date

angel0v90.yxmd