Generating EOMonth rows in a dataset where no items in a given month exist.

[TommyGoodone]

Hi Guys,

I am running a a macro to generate interest accrual on a monthly basis for a large amount of transactions.

Part of the macro finds the EOM (accrual period) using the existing data for each transaction. as most transactions are paid weekly, each month generally has data so for the most part it works well.

there are however some transactions that have a long gap between payments, meaning the data input looks something like this:

Amount	Rate	Date	EOMonth
50000	12.60%	1/06/2020	30/06/2020
17000	12.60%	1/07/2020	31/07/2020
17000	12.60%	1/09/2020	30/09/2020
17000	12.60%	1/10/2020	31/10/2020

as you can see, there's no entries in August, so when my ledger generation runs, the total amount of interest accrued is correct across the entire transaction, but there's no line items for August.

I believe I'll need to use a Generate Rows formula, but cannot seem to work my way around it.

essentially the required output with the above example would be:

Amount	Rate	Date	EOMonth
50000	12.60%	1/06/2020	30/06/2020
17000	12.60%	1/07/2020	31/07/2020
\N	12.60%	\N	31/08/2020
17000	12.60%	1/09/2020	30/09/2020
17000	12.60%	1/10/2020	31/10/2020

Kind regards,

[MarqueeCrew · Accepted solution]

Hi @TommyGoodone ,

 you can use my generate date rows macro:

https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14

Min and max dates as start and end.  You can use a multi row formula to look for month end.  

filter for those dates and include with a job and Union.

lots of trees helping hints.  

cheers,

 mark 

[TommyGoodone]

Hi MarqueeCrew,

Thanks for the quick and useful response.

I've spent some time working through your logic and what I'm trying to achieve but seem to have come close, before ending up in a circle. I am guessing I've overlooked something quite simple.

Essentially I've created a new row for every day between the max and min, then found the EOMonth and grouped by that, before joining back up with the source data which gives this:

Amount	Date	Rate	Right_TID	TrueEOM
50000	1/06/2020	12.60%	1	30/06/2020
50000	1/06/2020	12.60%	1	31/07/2020
50000	1/06/2020	12.60%	1	31/08/2020
50000	1/06/2020	12.60%	1	30/09/2020
50000	1/06/2020	12.60%	1	31/10/2020
17000	1/07/2020	12.60%	1	30/06/2020
17000	1/07/2020	12.60%	1	31/07/2020
17000	1/07/2020	12.60%	1	31/08/2020
17000	1/07/2020	12.60%	1	30/09/2020
17000	1/07/2020	12.60%	1	31/10/2020
17000	1/09/2020	12.60%	1	30/06/2020
17000	1/09/2020	12.60%	1	31/07/2020
17000	1/09/2020	12.60%	1	31/08/2020
17000	1/09/2020	12.60%	1	30/09/2020
17000	1/09/2020	12.60%	1	31/10/2020
17000	1/10/2020	12.60%	1	30/06/2020
17000	1/10/2020	12.60%	1	31/07/2020
17000	1/10/2020	12.60%	1	31/08/2020
17000	1/10/2020	12.60%	1	30/09/2020
17000	1/10/2020	12.60%	1	31/10/2020

my next step would then be to remove the superfluous rows (i.e. where Datetimemonth,[TRUEEOM] Month != to Date(month)) however this takes me back to having the month of august removed entirely as there was no original input in August 😞

I've uploaded my flow to show what I've put together so far trying to solve this, but would appreciate any assistance on this one.

Thanks,

Tommy.

[TommyGoodone]

Hey - a quick update.

I used a union followed by 3 multi row formulae and seem to have solved the problem!!!

I've attached the final output. at this stage making it cleaner would be all that I'd like to look into as it'll reside within an already large calculation in a batch Macro.

Kind regards,

Tommy

[MarqueeCrew · Accepted solution]

How about turning 2020-09-30 into a group by field left([field],6) and then SUMMARIZE AND found LAST for that Day. 

now you only have last days of month. 

Join on Date

all records from the last day that exist in the "right" output are the ones that you need to UNION into the original Left data (prior to the join). 

sorry my wife is killing my brain cells by watching the bachelor and I'm not able to move to my computer. 

cheers,

 mark

[Qiu · Accepted solution]

@TommyGoodone 
Maybe this is what you need. Appreciate you would mark it as accept if you like it.

[TommyGoodone]

Hi Qiu,

thanks for that. the output looks exactly like what I'm going for.

are you able to upload the file so I can view each of the pieces in more detail?

Thanks,

Tommy

[Qiu]

@TommyGoodone 

Just updated the reply. I must have forgot the attach it.

Thank you for the accept mark.