Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Reconciling Account balance to invoices over a period

BA
5 - Atom

Hi Team,

 

I have some data which I am trying to figure out how much an account balance reconciles to invoices over a period of time for each account. There are 2 calculations I am trying to do:

 

  1. Assign the 'amount to reconcile' (col 3) to the invoice amounts replacing what is there.  If there is a shortfall to replace then what ever is remaining should go there replacing the invoice amount.

 

  1. The remaining balance left if the Account balance is greater than the invoice amount.

 

Original Data:

Account#Invoice IDAmount to Reconcile26/04/202125/07/202124/10/202123/01/202224/04/202231/07/202230/10/2022
12345A567844.933.76      
12345A97544.93 25.6     
12345B65644.93     1.2 
56789C68695125.61  126.78    
98755H86421.32      25.78
19765D9594128.93   66.33   
19765H494850128.93    1.2  
19765K388343128.93    20.02  
98056C68695576.78 456.78     
98056C68695576.78  235.87    

 

Desired Outcome:

Account#Invoice IDAmount to Reconcile26/04/202125/07/202124/10/202123/01/202224/04/202231/07/202230/10/2022Remaining balance which did not reconcile ( col B - sum of col C to I)
12345A567844.933.76      41.17
12345A97544.93 25.6     15.57
12345B65644.93     1.2 14.37
56789C68695125.61  125.61    0
98755H86421.32      21.320
19765D9594128.93   66.33   62.6
19765H494850128.93    1.2  61.4
19765K388343128.93    20.02  41.38
98056C68695576.78 456.78     120
98056C68695576.78  120    0

 

I have attached this in a spreadsheet if it is easier to play around with (see raw data tab in attached file).

 

Happy to answer any questions or provide further clarity.

 

Thanks in advance :)

 

 

 

 

 

1 REPLY 1
gawa
16 - Nebula
16 - Nebula

hi @BA 

 

I believe that I could realize your demand by attached workflow, however if not, please let me know.

Personally it was a kind of fun for me like solving weekly challenge :)

image.png

Labels
Top Solution Authors