Hi,
I am trying to write a formula in Alteryx based on a template I have created in Excel to calculate cascading prior months amounts. I have attached an excel file to illustrate the formula I am trying to create.
To calculate the total for 1-2017 in cell C25, this sums the amount in the current month (Month 0 column) + the amount from the prior month (Month 1 column) + the amount from two months prior (Month 2 column) + the amount from 3 months prior (Month 3 column).
See attached excel example.
Does anyone know how to create this in Alteryx?
Solved! Go to Solution.
Using a mult-row formula tool (set for Num Rows = 3 in configuration), the following formula can be used to create a column (TOTAL) on each record.
[Month 0] + [Row-1:Month 1] + [Row-2:Month 2] + [Row-3:Month 3]
Your data however needs to be configured to start reading data on line #2 (Input tool option #7). In the real data, you should be okay, but in this mock data, you need to use a SELECT tool and make sure that all of the month data is numeric.
Cheers,
Mark
Thanks! This worked perfect.
Appreciate the prompt response, too.