Iterating through rows (Remember a variable for next Row)

Question

Hi,

I have this data (example)

DateIDFieldAmount24.09.20181Expenditure-5.0025.09.20182Income10026.09.20183Expenses-3.0020.10.20184Income20021.10.20185Expenses-4.00

and want to create this result

DateIDFieldAmountMonth24.09.20181Expenditure-5.00September25.09.20182Income100October26.09.20183Expenses-3.00October20.10.20184Income200November21.10.20185Expenses-4.00November

So i want to create a new Field Month, which is based on a certain transaction (Income) which is for me the new Starting Point of a month. All the transactions coming into this period until next income Should be in the month.

danilang · Accepted Answer

Hello @asadee

The trick here is to use the multi-row formula tool to generate the Month column based on what has previously occured

First create a DateForSort field which is an Alteryx date. Then fill all the Month information you can.  At this point you have the correct Month for all rows that fall after the 1st Income row.  You're still missing any expenses that come before this row.  You fix this by sorted the list by date descending and then filling in the blank Months which are now at the bottom of the list.  Sort Ascending and clean up the extra row and you're done

Dan