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: AmountRateDateEOMonth5000012.60%1/06/202030/06/20201700012.60%1/07/202031/07/20201700012.60%1/09/202030/09/20201700012.60%1/10/202031/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: AmountRateDateEOMonth5000012.60%1/06/202030/06/20201700012.60%1/07/202031/07/2020\N12.60%\N31/08/20201700012.60%1/09/202030/09/20201700012.60%1/10/202031/10/2020 Kind regards,

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

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,

Batch Macro

Accepted answers

MarqueeCrew

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

MarqueeCrew

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

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

1118-TommyGoodone.yxmd

All comments

MarqueeCrew

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 😞