Hi,
I have this data (example)
Date | ID | Field | Amount |
24.09.2018 | 1 | Expenditure | -5.00 |
25.09.2018 | 2 | Income | 100 |
26.09.2018 | 3 | Expenses | -3.00 |
20.10.2018 | 4 | Income | 200 |
21.10.2018 | 5 | Expenses | -4.00 |
and want to create this result
Date | ID | Field | Amount | Month |
24.09.2018 | 1 | Expenditure | -5.00 | September |
25.09.2018 | 2 | Income | 100 | October |
26.09.2018 | 3 | Expenses | -3.00 | October |
20.10.2018 | 4 | Income | 200 | November |
21.10.2018 | 5 | Expenses | -4.00 | November |
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.
Solved! Go to Solution.
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
Thanks. This is great!!! I was experiencing with it. It is pitty, that alteryx doesn't show "Autosuggest" during Coding in Multiple Field Row.