Hi all,

I have 2 repeating fields, Turnover last year & turnover this year, each with 12 repetitions.

I would like to compare the year to date turnover with same period last year.

EG If the current year has 3 months of turnover, the 3 first months have to be compared with the 3 first months of last year, and so on.

So using the COUNT function, I can determine that 3 months of this year have Turnover.

How can I dynamically make the sum of only the first 3 months of last year although all 12 repetitions cells have a value?

I was thinking of using the let function to avoid a lenghty calculation, but I don't grasp how to make the recursion...

Thanks for helping me out!

Recursive calculations are done through custom functions. You could define a custom function as:

SumRepetitions ( repeatingField ; maxRepetition ) =

GetRepetition ( repeatingField ; maxRepetition )
+
If ( maxRepetition > 1 ; SumRepetitions ( repeatingField ; maxRepetition - 1 ) )

and call it as =

SumRepetitions ( LastYear ; Count ( ThisYear ) )

 

Although with only 12 repetitions, you could do just =

Let ( [
n = Count ( ThisYear )
] ;
Sum ( 
If ( n ≥ 1 ; LastYear[1] ) ; 
If ( n ≥ 2 ; LastYear[2] ) ; 
If ( n ≥ 3 ; LastYear[3] ) ; 
If ( n ≥ 4 ; LastYear[4] ) ; 
If ( n ≥ 5 ; LastYear[5] ) ; 
If ( n ≥ 6 ; LastYear[6] ) ; 
If ( n ≥ 7 ; LastYear[7] ) ; 
If ( n ≥ 8 ; LastYear[8] ) ; 
If ( n ≥ 9 ; LastYear[9] ) ; 
If ( n ≥ 10 ; LastYear[10] ) ; 
If ( n ≥ 11 ; LastYear[11] ) ; 
If ( n ≥ 12 ; LastYear[12] )
)
)

 

Edited March 12, 20196 yr by comment

Great! Thanks!