Forum Discussion
What do you think of thunks?
This thread is mainly to illustrate technique, not to solve concrete task. However. Perhaps I missed something, but all solutions take 2 records back and 2 records forward for the range. Not dates. What if we take the range for dates (assuming they are not sequential and some could be missed), i.e. [Date] -2, not two records back ?
- djclementsJan 22, 2025Silver Contributor
Introducing new parameters to a problem often means considering a new approach. For example:
=FILTER(Data, MAXIFS(Data[Value], Data[Date], ">=" & Data[Date] - 2, Data[Date], "<=" & Data[Date] + 2) = Data[Value])However, I do respect PeterBartholomew1 for his quest to find a generalized approach for any given situation. My answer to the question, "What do you think of thunks?", remains the same: I think they're great for some situations and overkill for others. I would highly encourage anyone who's taken an interest in this sort of thing to explore the concepts that Peter has shared, but to also explore alternative methods as well. The more you know, the better your chances will become for finding an appropriate solution to any problem. ;)
- PeterBartholomew1Jan 21, 2025Silver Contributor
I have my prejudices, but I would expect my approach to become stronger. If, say, XLOOKUP were used to identify first and last cells in each range, then the range ":" operator would return a variable length range for each mapped value. I could put the search criterial within a Lambda function to modularise the problem and then return the ranges within thunks (ranges, especially of varying size would throw an error within MAP).
I can then perform the calculations on the individual time slices of the data table with a second application of MAP (or, better, a custom version of MAP that expands the thunks automatically before passing the range to the function specified by the formula
MAPϑ = LAMBDA(ϑ, FNλ, MAP(ϑ, LAMBDA(ϑ, FNλ(ϑ()))) );The strategy would be to break the calculation into a hierarchy of Lambda functions, each one with a well defined task. In essence, I propose to use thunks as a 'pass by reference' strategy, as opposed to 'pass by value'.
- m_tarlerJan 21, 2025Bronze Contributor
Isn't this statement the 'essence' of Thunks?
In essence, I propose to use thunks as a 'pass by reference' strategy, as opposed to 'pass by value'.
- PeterBartholomew1Jan 22, 2025Silver Contributor
Maybe. My thoughts only went as far as "I am using these objects as if they were variables passed by reference". I hadn't got as far as considering whether they might actually be that! My past experience as a FORTRAN programmer didn't involve much is the way of philosophy.
- m_tarlerJan 21, 2025Bronze Contributor
I would think Daniel's approach could be easily adapted to that:
=FILTER(C3:D25,MAP(C3:C25,LAMBDA(x,AND(XLOOKUP(x,C3:C25,D3:D25)>N(FILTER(D3:D25,(x-2<C3:C25)*(x+2>C3:C25),0)))))))