Jump to content
Sign in to follow this  
pmassin

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 this post


Link to post
Share on other sites
Posted (edited)

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
  • Like 1

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.