Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Running Total for A/R balances for months with no activity

Highlighted
11 - Bolide

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.

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
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.
Highlighted
Alteryx
Alteryx

Hi Alex,

 

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.

 

Kane

Highlighted
8 - Asteroid

Thank you. Very helpful.

Labels