Dynamic Multi-Row Formula

[mjh418]

Hi All, 

I need help with making a dynamic multi-row formula. 

Calculation itself is a simple compounding. Where N is the first value(row), [(1+Value(n))*(1+Value(n+1))*(1+Value(n+2))]-1...so on. It is actually equivalent of =FVSCHEDULE(1,Value(n),Value(n+1),...)-1 in EXCEL which also seems to exist in Alteryx as FinanceFVSchedule.

The tricky part is that the period (number of rows) is not fixed. It can vary on the workflow outcome. So maybe its 6 rows sometimes, or maybe its 12 rows sometimes. 

If someone can help me with a formula/method to calculate compounding of changeable number of values(rows)

Type	Value	DESIRED/EXPECTED OUTCOME
A	-0.00419	-0.004193025
A	-0.09329	-0.097093214
A	-0.1191	-0.204630879
A	0.101437	-0.12395137
A	0.036945	-0.091586132
A	0.022284	-0.071342618

example:

First outcome:  -0.00419 +1 = -0.995806975 -1 = -0.004193025

Second outcome: (1+-0.00419)*(1+0.09329) -> (0.995806975 * 0.906708638)-1 = -0.09709

The Main issue is that the number of rows(periods) will change, so it needs to be dynamic. I can't just hard-code rows or fields.

Calculation itself isn't very important here. I just need to know how to have a multi-row or multi-column formula that will work dynamically even if the number of rows or columns change.

If there is way to do this by column instead of rows, that's even better.

Thank you so much for your time.

[mjh418]

Pingu, Thank you very much! 
It was actually rather simple..but I think it didn't work for me because I tried to create a new field instead of update the existing field which gives me incorrect figures. 

If I had the values as columns and types as columns,  for example,

	Jan-2020	February-2020	March-2020
A	0.11	1.32	0.00223
B	0.35	0.23	0.0523
C	0.01	0.65	0.234
D	0.0662	0.23	0.723

Do you think its possible to use COLUMNS , for example, [(January value+1)*(February value+1)]-1 assuming that the columns will change? Some times it will only contain Jan to March; sometimes Jan to December.

Thanks so so much.

[mjh418]

@mceleavey Thank you , you helped me to get the results while creating a new column at the same time. 

If you don't mind helping me once more, If I had the values as columns and types as columns,  for example,

	Jan-2020	February-2020	March-2020
A	0.11	1.32	0.00223
B	0.35	0.23	0.0523
C	0.01	0.65	0.234
D	0.0662	0.23	0.723

Do you think its possible to use COLUMNS , for example, [(January value+1)*(February value+1)]-1 assuming that the columns will change? Some times it will only contain Jan to March; sometimes Jan to December.

Thanks so so much.

[mceleavey]

Hi @mjh418 ,

No problem, and yes. The easiest way is to pivot the data using transpose:

Which will put the data into the same format as before, and then you can apply the same calculation, but in the multi-row formula check "Key" in the "Group by" section.

M.

[Pingu]

@mceleavey Example creates a new field as it looks like. Both way works. Then in the expression you just need to refer to the new calculated field instead of the original value for example.

It is definitely possible what you ask, for example if you want you can go from all the columns to rows with the Transpose tool and do your calculation. 

[mjh418]

@mceleavey 

Thank you for the response.

Will your new suggestion work even if the number of columns change? In the screenshot, it shows January, February, but sometimes the month of January and February won't exist at all.  Like sometimes there will only be January to June, sometimes only June to December. Will it recognize it without me having me make any hardcoded adjustment?

Do you have a workflow file I can see?

Sorry for taking your time and thank you once again. 

[mjh418]

@mceleavey 
I tried it and it kind of works!! Which is great, but there seems to be a new issue.

For the KEYs that do not have any initial value(as in for example, no January value(null) but there are values from March to December, for example, it won't give any Calc1 Value. Any suggestions?

[mceleavey]

@mjh418 ,

yes, 100%.

I've built the new one for you:

This uses a tool I built which allows you to group a record ID by a given field or fields. I've attached this for you.

[mceleavey]

@mjh418 ,

It depends how you wish to calculate it. I would maybe suggest applying a filter immediately following the transpose tool to remove any null rows. This will then begin the numbering from March, for example.

M.

[mjh418]

Yes Filtering nulls definitely worked!!

I can't seem to open packages (maybe its my version problem) but is it possible to reattach your Grouped ID one as yxwz?

[mceleavey]

@mjh418 ,

It's a macro file, so I've attached it unpackaged. Just drop it into your macros folder.

M.