Can a Recursive custom function refer back to a previous recursion (so it can perform one part only once)

[databaser]

I'm curious about the above question in general, but I'll give an example to clarify (I'm sure if I could figure this out once, I'd make use of it in other applications in the future).

I'm trying to write a recursive custom function that would list the highest numbers in `overlapping sub lists` within a list.

say you had a list of how many milliliters of water a mouse drank each day in chronological order, and then you wanted a list of the most ml the mouse drank on any day of overlapping 3-day periods...

let's call the function 'peaks'

you would use:

peaks(valuelist ; 3)

...

peaks(

"10

20

30

40

50

60

70

80

90";3)

would return:

30

40

50

60

70

80

90

because the highest of the 1st, 2nd, and 3rd values is 30, the highest of 2nd, 3rd, and 4th values is 40...

I can write this using this custom function - http://www.briandunning.com/cf/593, where SortArray ( "4¶2¶11" ; "Des" ; "Number" ) returns "11¶4¶2"

It would be easy to do it where it has to use that function once for every value in the returned list, but if the 'period length' is high, I feel like that makes the function take much longer to calculate than it should.

Here's how it would be easy to do (but probably take an unnecessarily long time to calculate):

peaks ( valuelist ; period ):

GetValue(SortArray ( leftvalues(valuelist ; period) ; "Des" ; "Number" );1) & (if valuecount (valuelist) > period ; ¶ & peaks (middlevalues(valuelist ; 2 ; valuecount (valuelist)) ; period))

However, if there were a way for recursive custom function to essentially refer back to the previous recursion (and I'm thinking there might be and I just don't get it), I think this could be done in a way where it could be calculated much faster.

Ideally, it would calculate this - getvalue(SortArray ( leftvalues(valuelist ; period) ; "Des" ; "Number" );1) - only in the instances when the peak decreases (there'd be a way to do it where you don't even need it when the peak decreases, by keeping track of 2nd highest values, but I don't want to think that through right now - though that's probably how i'd do it if i could figure out the broader problem first).

That would be the first value of the result. Then, it would repeat by using the previously calculated value (I guess this is what i don't know how to do).

then you could do it like this, and the function should run much faster:

let (a = the previously calculated value;

//when applied to middlevalues(valuelist ; 2 ; valuecount(valuelist)) in the recursion...

if (getvalue(valuelist; period)>a ; getvalue(valuelist; period) ;

if (patterncount(¶ &leftvalues(valuelist; period)&¶ ; ¶ & a&¶ )>0 ; a ;

getvalue(SortArray ( leftvalues(valuelist ; period) ; "Des" ; "Number" );1)

)

If subordinate custom functions are necessary for this type of thing that's fine. If there is a way for a recursive custom function to refer back to past recursion I know I would make use of that A LOT. If there's another way to solve this problem that would be interesting too.

[databaser]

I think I just got it ... add a parameter to store previous values

(I'm still thinking that through)

[comment]

I am afraid you have lost me here:

However, if there were a way for recursive custom function to essentially refer back to the previous recursion (and I'm thinking there might be and I just don't get it), I think this could be done in a way where it could be calculated much faster.

Ideally, it would calculate this - getvalue(SortArray ( leftvalues(valuelist ; period) ; "Des" ; "Number" );1) - only in the instances when the peak changes

I don't quite see how that would make the calculation faster - i.e. how would you know if the peak changes without calculating it first.

In any case, a recursive function cannot "see" the result of a previous recursion unless that result is stored somewhere - either inside a parameter sent down to the current recursion, or in a $variable. You could also recalculate the previous result during the current recursion - provided you still have the data required to make such calculation.

[databaser]

With variables, isn't it difficult and/or sometimes-impossible to keep it from being recalculated in each recursion?

Also, are variables with $ in front different than ones without it (is that how you keep them from being recalculated?)

In terms of why I think it would take longer to calculate, I think if the 'period' is like 30 or greater, each sort would take at least tenths of a second. if my list has 500 values, and the period is 30, it would seem like that could easily waste more than 5 seconds (and if I need to perform the calculation about 1000 times a day, that's over an hour/day of unnecessarily waiting for the calculation) UPDATE: briandunning.com gives this guidance on calculation time: Performance on a 1,66 GHz Mac Intel Core Duo: 50 word array: 60 msec. 100 word array: 150 msec. 500 word array : 1,1 sec. Sorting of dates are 30% faster. Still 1000 may underestimate how many times/day I would need to use this recursive custom function so using the sortarray function less could still potentially save me at least 30 minutes/day of waiting for calculations.

Edited November 26, 2011 by databaser

[comment]

With variables, isn't it difficult and/or sometimes-impossible to keep it from being recalculated in each recursion?

It should be recalculated in each recursion. The idea is to use it before it's recalculated - same as you would do with a parameter.

Also, are variables with $ in front different than ones without it

They are different in scope: a variable without a $ or $$ prefix will expire as soon as the Let() function is evaluated; the other types persist until the end of the current session.

I am afraid I still don't see how having access to the previous result will save a calculation.

Still 1000 may underestimate how many times/day I would need to use this recursive custom function

You intend to perform this calculation every minute and half, 24 hours a day?

[David Jondreau]

My understanding is that the Sort custom functions tend to slowness. What is you took the value list of the subset of numbers and found the Max? That could be done with a recursive custom function looping through the list or the judicious use of Evaluate and Substitute.

They are different in scope: a variable without a $ or $$ prefix will expire as soon as the Let() function is evaluated; the other types persist until the end of the current session.

$vars and $$vars also go into the function. If you've got a $counter in a script that calls a function that uses $counter, you're going to be really unhappy.

[databaser]

you're right. evaluate(max(...)) is much better than using getvalue(sortarray(...);1). That was just dumb. Thanks.