This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I need to have the running total of a customer's activity for all months with a balance even for months with no activity. When I use the running total tool, I only get running totals for months with transactions. Additionally, there should be no balance in months prior to a customers first date of activity and no balance in months after they have reached zero and have no activity in that month. I've attached a small file to illustrate.
If nobody solves this for you, I will do it in the morning.
Create a set of all month end dates. Add a field with a value of "join me" Create a unique set of account numbers Add a field with a value of "join me" Join these two sets of data on that field. Add a null field for balance. Union this with your real data Sort by acct, date and balance Create a multi row formula grouped by account number Fill in gaps Filter out null values
That's my approach.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
The attached module will show you how to do this. Essentially I have used the generate rows tool to create a row for each month and then filled in the details using a join and multi-row. This does not account for Opening and closing dates but if you Join a table with columns Customer ID, Open Date, Close Date on Customer ID, then you can use a filter tool to remove records or a formula tool to null() records.
You should be able to play with this to get it as Dynamic as you would like.