# Partial sum of repeating fields

## Recommended Posts

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!

##### Share on other sites

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 by comment

Great! Thanks!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...