We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Showing the reminder calculation process and output

8 - Asteroid

Hi Team,

Need your support to show the output below

take the first value on the right(Value1), mins the left value(Value). Doing the calculation until the right side value become 0 and show the right side value's reminder.

Thank you very much

Input

 Ref PastDate Value User Product Ref1 PastDate1 Value1 User1 Product1 100005 20220915 -10 F Apple 100001 20220910 50 A Apple 222003 20220918 -20 G Apple 10002 20220913 20 B Apple 22233 20221001 -60 S Apple 100002 20220915 30 C Apple

Output: (1Pair) - option one

 Ref PastDate Value User Product 100001 20220910 50 A Apple 100005 20220915 -10 F Apple 222003 20220918 -20 G Apple 22233 20221001 -30 S Apple 22233 20221001 -30 S Apple

Output (Muti-pairs) - option two

 Ref PastDate Value User Product 100001 20220910 50 A Apple 100005 20220915 -10 F Apple 222003 20220918 -20 G Apple 22233 20221001 -30 S Apple 10002 20220913 20 B Apple 22233 20221001 -20 S Apple 100002 20220915 30 C Apple 22233 20221001 -10 S Apple 100002 20220915 20 C Apple

Thx Again

4 REPLIES 4
Alteryx

@k3pineapple I believe you could approach a solution using the Append Fields tool to create all possible record combinations, and then a Multi-Row Formula tool to conduct the row-by-row calculation. I'm not understanding the logic completely to arrive at your solution above. If you could share a workbook with the calculations you are using, I can try to assist more.

8 - Asteroid

@MatthewO Thank you very much for your help here. This is my current workflow， which can generate the reminder now.

I need somehow put the reminder into the this calculation

Rule #1

in this image, 1st row 50-10 get 40, I would like to let 40-20(row 2) first instead of 20-20

Rule #2

After caluculate 40-20, get 20, use this 20 to minus 60, since the result(20-60=-40) is smaller than 0.... -60 will split to -20 & -40

-20 will make the balance as 0.

-40 is going to match the 2nd value:20 in the value column....since 20-40 =< 0 , -40 will split into -20 & -20

1st.-20 is going to match 20 to be 0, 20-20 =0

2nd -20 is going to match 30, 30-20=10

Not sure this logic can be applied in the Alteryx, may be use some python scripts? to avoid the confusion I removed the user column no need for that.

Thank you again.

Alteryx

@k3pineapple I was able to replicate your sample output by using an Iterative Macro. I have attached the macro as well an example of it's usage in a workflow. You could continue to tune the logic to suit your needs but I hope this is helpful for you.

8 - Asteroid

@MatthewO Thank you so much for your kind assistance. this macro helps me resolve the reminder problem.

I know maybe it is a little too much here...I hope you still may help me on this

Kind wondering whether you may help me to add a sector in order to compare the PastDate columns in this macro?

Such that

if the Pastdate < Pastdate1 ( ie. 20221001 < 20220930) calculation side reverse, using Right_Value matching the Value columns. the reminder from Right_Value will be the start value to match Value

Data Source:

 Ref Cdate PastDate Value Ref1 Cdate1 PastDate1 Value1 100001 20221101 20220910 50 100005 20221101 20220915 -10 10002 20221101 20220913 20 222003 20221101 20220918 -20 100002 20221101 20220915 30 22233 20221101 20221001 -60 200010 20221101 20220915 40 330000 20221101 20221002 -300 300005 20221101 20220915 50 322201 20221101 20221003 -300 130001 20221101 20221003 20 353330 20221101 20221003 -30 32222 20221101 20221006 300 322222 20221101 20221005 -20 315532 20221101 20221007 300

Calculation Process:

Direction switch has been mark in red

Output

 Ref Cdate PastDate Value 100001 20221101 20220910 50 100005 20221101 20220915 -10 222003 20221101 20220918 -20 22233 20221101 20221001 -20 10002 20221101 20220913 20 22233 20221101 20221001 -20 100002 20221101 20220915 30 22233 20221101 20221001 -20 330000 20221101 20221002 -10 200010 20221101 20220915 40 330000 20221101 20221002 -40 300005 20221101 20220915 50 330000 20221101 20221002 -50 330000 20221101 20221002 -200 130001 20221101 20221003 20 32222 20221101 20221006 180 322201 20221101 20221003 -300 32222 20221101 20221006 120 315532 20221101 20221007 180 353330 20221101 20221003 -30 315532 20221101 20221007 30 322222 20221101 20221005 -20 315532 20221101 20221007 20 315532 20221101 20221007 70

Thank you Again

Labels