community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Crosstabbed Aging Table with Netted Values for Debits

Atom

Hello. Scratching my head as to how to design a wf for a new accounting task and was hoping for some direction and/or ideas.

 

I need to create an aging table for every account. The starting point for any given account is a monthly balance summary and an accumulated total balance (shown in the first table).

 

PeriodAmountRunning Total
5/31/2018    (5,000.00)            (5,000.00)
6/30/2018    25,253.85            20,253.85
7/31/2018    (3,046.15)            17,207.70
8/31/2018          253.85            17,461.55
9/30/2018          253.85            17,715.40
10/31/2018          253.85            17,969.25
11/30/2018          253.84            18,223.09
12/30/2018        (100.00)            18,123.09

 

The ask is for this data-set is for it to be transformed into the aging table shown below. Here are some of the required logic details:

  • Debits should only appear in the aging buckets if there is no prior balance to offset them, otherwise the net balance should be shown and the aging reset. (first two rows)
  • Debits will offset balances in the oldest aging buckets first (see the last row/last column debit of $100).
  • Credit activity will offset any debits and the net will age into the first bucket. Subsequent credit activity will shift older balances across into more aged buckets.
  • aging should be determined from the date of the first transaction. there may be gaps (months without activity)
Period Amount  Running Total 1-30 31-60  61-90  91-120  121-270 
5/31/2018    (5,000.00)            (5,000.00)                       (5,000.00)    
6/30/2018    25,253.85            20,253.85                       20,253.85    
7/31/2018    (3,046.15)            17,207.70                          17,207.70   
8/31/2018          253.85            17,461.55                             253.85                          17,207.70                                               -  
9/30/2018          253.85            17,715.40                             253.85                               253.85                             17,207.70 
10/31/2018          253.85            17,969.25                             253.85                               253.85                               253.85                               17,207.70
11/30/2018          253.84            18,223.09                             253.84                               253.84                               253.84                                  253.85                              17,207.70
12/30/2018        (100.00)            18,123.09                                253.84                               253.84                                  253.84                              17,361.55

 

I've been playing with adding sequence numbers and creating the aging logic based on that but that's as far as I've gotten. Not sure how to accomplish these custom netting rules. Any help is appreciated!

Community Operations Manager
Community Operations Manager

@Rondo,

 

You have a lot of logic to configure here based on your description. I see this being a Formula tool and Multi-Row Formula tool to create the logic. If you could post what you have tried thus far the Community could help direct you.

 

DanM

Labels