Multi-Row Formula Tool: Month Over Month Variance

[amandaj4123]

I could really use some expertise here.  I've attached a dataset, also below, which is appended with new data every month, for 12 months.  As of now, I have the last month of previous year, for comparison, and Jan-Mar.  For each unique "Identifier",  I need to calculate the month-over-month variance of Amt2.

(i.e. JAN Amt2 divided by 2018DEC Amt 2, FEB Amt2 divided by JAN Amt2, etc) I have to keep in mind that the formula must adjust to the number of months currently within the dataset, and still process successfully with new months added.  Your help is greatly appreciated!

CONTROL	Identifier	#Mths Loaded	Earnings Mth	Effective End Date	Mth#	Amt1	Amt2	Amt3	Month over Month Variance
2018DEC12345678Canada	12345678	4	2018DEC	2019-01-31	201812	7520.32	7520.32	7520.32
JAN12345678Canada	12345678	4	JAN	2019-02-28	201901	6432.01	0.00	6432.01
FEB12345678Canada	12345678	4	FEB	2019-03-31	201902	6432.01	6432.01	0.00
MAR12345678Canada	12345678	4	MAR	2019-04-30	201903	8445.63	6483.85	10000.00

IF [Row+1:IDENTIFIER]=[IDENTIFIER] THEN [Row+1:Amt2]/[Amt2]) ELSEIF [Row+2:IDENTIFIER]=[Row+1:IDENTIFIER] THEN [Row+2:Amt2]/[Row+1:Amt2] ELSEIF [Row+3:IDENTIFIER]=[Row+2:IDENTIFIER] THEN [Row+3:Amt2]/[Row+2:Amt2] ELSEIF [Row+4:IDENTIFIER]=[Row+3:IDENTIFIER] THEN [Row+4:Amt2]/[Row+3:Amt2] ELSEIF [Row+5:IDENTIFIER]=[Row+4:IDENTIFIER] THEN [Row+5:Amt2]/[Row+5:Amt2] ELSEIF [Row+6:IDENTIFIER]=[Row+5:IDENTIFIER] THEN [Row+6:Amt2]/[Row+5:Amt2] ELSEIF [Row+7:IDENTIFIER]=[Row+6:IDENTIFIER] THEN [Row+7:Amt2]/[Row+6:Amt2] ELSEIF [Row+8:IDENTIFIER]=[Row+7:IDENTIFIER] THEN [Row+8:Amt2]/[Row+7:Amt2] ELSEIF [Row+9:IDENTIFIER]=[Row+8:IDENTIFIER] THEN [Row+9:Amt2]/[Row+8:Amt2] ELSEIF [Row+10:IDENTIFIER]=[Row+9:IDENTIFIER] THEN [Row+10:Amt2]/[Row+9:Amt2] ELSEIF [Row+11:IDENTIFIER]=[Row+10:IDENTIFIER] THEN [Row+11:Amt2]/[Row+10:Amt2] ELSEIF [Row+12:IDENTIFIER]=[Row+11:IDENTIFIER] THEN [Row+12:Amt2]/[Row+11:Amt2] ELSE "" ENDIF

[OllieClarke · Accepted solution]

If you group by indentifier in the multi-row formula tool, then the formula

[Amt2]/[row-1:Amt2]

will give you your monthly variance. (the nulls are from dividing by 0, and for being the first row - you can change this in the 'Values for Rows that don't Exist' settings

 

[amandaj4123]

Well, this certainly worked!  Thanks for the simplification.  I guess I was just too close to it to see it.