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

Best way to calculate row by row as next row requires calculation in prior row

nviejep
5 - Atom

Capture.PNG

 

Hi there. Probably a simple question compared to some of the others I have seen on here, but I am trying to replicate something similar to the excel spreadsheet above. The expense, income, and net change data is there. However, I need to create the opening, generation(utilization), and closing columns.

 

The 2018 opening is the only number given. Generation/Utilization is the smaller of opening or absolute value of net change). Closing is open + generation/utilization. And then next year's opening is just prior row's closing.

 

Is there a simple way to do this? I've been trying to use multiple columns and multi-row/field formulas to no success. Thank you!

9 REPLIES 9
tcroberts
12 - Quasar

I've whipped together a solution that first Transposes the data, then uses the Multi-row formula to generate the values, before CrossTabbing back to the original form.

 

I essentially look for the value of the "name" field that I want to update, and set the value based on position relative to the others. If your columns are not always in the same position, you may want to sort before applying the multirow.

 

You'll also probably need to tweak the row numbers a bit, as I didn't include the Expense or Income fields, but this will give you the gist of it.

 

 

MultirowMultifield.PNG

 

 

Let me know if this works for you, or you have any questions:

 

CharlieS
17 - Castor
17 - Castor

This sheet needs to be solved one year at a time. To achieve this calculation, I built an iterative macro using the [Year] field to control iterations. 

 

Check out my attached solution and let me know if you have any questions.

 

 

nviejep
5 - Atom

Thank you. This worked great.

weiweishen
7 - Meteor

 

CharlieS
17 - Castor
17 - Castor

@weiweishen wrote:


The macros should be included in the .yxzp package. I made an adjustment to the relative references in the attached package  that might work better for you. When you open then package, be sure to open and run "20200227-MultiRowWorkflow.yxmd"

weiweishen
7 - Meteor

Thank you so much. I got the macro and the workflow. Have a great weekend.

weiweishen
7 - Meteor

Charlie

Thank you again for your help.

In my specific case, I want to prepare a Loan Amortization schedule. Please see the attached Excel file. The Excel file shows the result that I want. The # of payment is 20, the only known information is $30,000 book value of the loan, 0.02 annul interest rate and $442.51 fixed payment. From the first payment, the beginning balance is the $30,000 book value, the monthly interest is 30,000*(0.02/12)=50, the ending balance is beginning balance + monthly interest - monthly payment, which is $29,607.49. Now the 29,607.49 become the 2nd payment beginning balance, the exercise will be repeated again and again until to 20 payments. I prepared the following Macro and the workflow. But the iterative macro can only perform the exercise for one period. I don't know whether you can help?

 

Thank you.

weiweishen
7 - Meteor

Here is the attachments

CharlieS
17 - Castor
17 - Castor

Hi @weiweishen 

 

It looks like you've got your own use case that isn't related to this topic. I'd suggest using this information to submit at new topic to Designer Discussions. This way it will be a new post that isn't marked as "solved" and you're more likely to get help from the Community. 

Labels