Formula to calculate cascading amounts in prior months

Question

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?

Cascading payment example.xlsx

MarqueeCrew · Accepted Answer

@Jaslusser12,

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