Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Logic help - offset Overpayment to ShortPay _ Please Advise. (Mutlipe row fomula)

florayaoyao
8 - Asteroid

Hi Alteryx Gurus, 

 

I am stuck with applying the a logic in Alteryx, please share some thoughts.  Any advise is much appreciated!  

 

I have customers with open deductions and overpayments, so the goal is offset the deduction items by the overpayments items, below are two customer examples(with expected result), and I need a logic to create the column J - "Original amt uploaded to Web ADI Flag" and "Uploaded amount" in Alteryx.

 

LOGIC:

 

If column E - "DED offset completed flag" is "N" (example- customer ABC Inc), column J will apply all "Y" to the overpayment items( as all overpayments are going to be used for offset), and uploaded amount should be the same as Column G - remaining amount, but for deduction items, it will look up the items in order by claim age (oldest first) for offset.  In this case, total  OPM is -$404 (column B) , so we are only need to offset $404 of deduction, therefore, for uploaded amount, I need to put $404 there ( instead of original amount $638), and for the other two deductions left there, the uploaded amount should be 0.

 

Another situation, for customer 123 solution below,  is in the opposite case, where there are more overpayments than deductions,  so then for column J, all deduction items will apply "Y", but for overpayment, it will look the offset by claim aging (column). The oldest one of - $151.41 could be used fully, and second oldest one of -$480,20 will only be used with amount of $141.86 partially. The total of one customer in "uploaded amount" needs to be "0".   (My biggest challenge is here to create a logic if there are multiple items could be used to offset but not the full amount and  follows the rule by claim age (old first)….

 

 

I am assuming that multiple rows formula might help in this case, but not sure how to do it...  Please help and advise. 

 

thank you thank you!

 

Flora

 

 

LOGIC OFFSET.JPG

 

 

 

 

2 REPLIES 2
Thableaus
17 - Castor
17 - Castor

Hi @florayaoyao 

 

I've spent part of the weekend trying to figure out your problem.

 

Here's what I came up with (might not be the most optimized workflow), but it seems to work:

SolutionOffset.PNG

- Format Date field for sorting purposes. Add RecordID to keep track of records (they will be split up).

- Format Amt Remaining S/B field and change it to Double type (easier to work with straight numbers).

- Create Sum_Amt field which is going to calculate the Running_Sum depending on DED_offset_Completed_Flag and Claim Class. This is so we can separate how we're going to deal with each situation.

- Whatever does not enter the Sum_Amt field (which is Null, by any chance) is going to be compared with the Running Sum created. This is so we can compare overpayment with deduction depending on the case. So we get the number and join it to the dataset.

- Sort by Bill To Addressee and Claim Formatted Date so we can apply multi-row formula according to the oldest claim (that's something really important).

- Copy Sum_Amt field to Sum_Amt_Ref - this is going to be useful to do comparisons

- Use Multi-Row Formula Tool to update Max_Sum_Amt field (the Running_Sum). This formula is just taking the last Sum_Amt and subtracting it by the Amt Remaining S/B

- Multi-Row Formula Tool is then again applied to create Uploaded Amount Field. I created a logic that might be a little bit confusing and hard to explain, but if you have any doubts, please let me know.

- Create Original Amt uploaded to Web ADI by checking if the next Uploaded Amount row is greater or equal than 0. 

- The rest is just unioning data and organizing it, formatting back to dollar and parenthesis style and unchecking unnecessary fields.

 

This was quite difficult to do but I hope you get it.

Have a nice week.

 

Cheers,

florayaoyao
8 - Asteroid

Thank you sooo much Thableaus,  for spending time to dig into it.  I truly appreciate your time and efforts on this.

 

I twist it a little bit, and I followed your logic to view it by groups and using the multiple rows formula.    It worked out!  :) 

 

I am now ahead of my deadline.. thank you!!

 

(Btw, some of your formatting formula is very helpful too). 

 

Kudos!! 

 

Best,

Flora

Labels