PROFIT

For references sake, I was able to complete the issue with a Value List. I added the below in my table as a dimension to display the categories:

If you change the subquery to:

Runaway backtesting inside of a function using a for-loop.

The solution is to remove the logic of f_find_back out of the function and you save thousands and thousands of generated nonsense code lines.

2 ways:

Screenshot below refers:

Google sheets here

IMPORTANT UPDATE: re: 2000+ rows, see link in my last comment (for OneDrive template, s.t. expiry, and bottom of this proposal for screenshot of error "too large to import")

fyi: (Office 365 - Excel - fns. such as 'filter' etc., so #Name! will be ubiquitous upon opening this link; however, sharing this should serve as a convenient means to replicate above screenshot in the correct version of Excel)

1. Office 365
2. Adequate space to the right or ability to utilise extra sheet for calcs as req.

High-level | Take-aways

• Relies on 2 additional tables: Qty 'depletion' table (here - G:P) and corresp. Profit Vectors (here - R:Z)

• Compressed representations (2x2 tables, per Summary Tables, here: rows 19:32) could be explored to simplify matters (albeit these are still Work in Progress ['WiP'])

• VB could be attractive alternative too

Low-level | Procedural

1. Single-cell function could not be found without exceptional complication (however, suspect it could be possible e.g. using FilterXML?)
2. Qty label transposes filtered negative quantities (grey shaded, G3: J3)
3. These values are depleted in turn (from left to right, i.e. dependency on having data sorted in ascending order by date (in this depiction, data first sorted by 'fruit' as these are assumed to be independent in the context of the profit calc.)
4. For example, -5 quantity (G3) offset by +10 (B3) to yield +5 which, in turn contributes towards the -35 qty (H3) to yield -30. No further contribution can be made (left to right); next row (5): +20 avail (given), no offset against -5 (G3, already 'reimbursed', so 20 offset against -30 (H4) to yield -10, and so forth.
5. Worksheet set up to accommodate longer list of fruit / profits
6. Result table of residual 'quantities' (~ G:J) applied to price differential to yield 'profit vectors' (R:U) which are summed against corresponding negative quantities to produce Profit score (yellow shaded cells)

Salient functions (all of which should be available within Google Sheet, but for completeness):

1) Profit calc. (E4, drag down):

To find the first instance of an email after any 'key_value' we can just check for the first email after it using a bool to check, we can then use the bool to check whether that key_value has found it's email.

You wouldn't use max(). If you want one row returned, you would use order by and limit/fetch first.

I find your question really hard to follow -- no sample data, no desired results, no simple explanation of what you want to do. But if you wanted the category with the maximum profit, it would look something like this:

You were close, but you need to use window functions. Without using window functions, the query will not look at the other rows in the case statements to calculate the GROSS column.

The query below is how to properly calculate the GROSS using window functions. I have added ROUND to round the gross to an integer, but you can remove that if you want the decimal points.

If you want to utilize caching, you have to check that cache first before you do a request. Otherwise, it won't make much sense.

And doing synchronous blocking requests is deprecated, and you should also switch over to use fetch if possible instead of XMLHttpRequest.

A function that first checks if the data is in the catch before doing the request could look like this:

While shading the area between intersecting lines may sound like an easy task, to the best of my knowledge it isn't and I struggled with this issue several times. The reason is that in general the intersection points (in your case the zeros of the curve) are not part of the data. Hence, when trying to shade the areas with geom_area or geom_ribbon we end up with overlapping regions at the intersection points. A more detailed explanation of this issue could be found in this post which also offers a solution by making use of approx.

Applied to your use case

1. Convert your Date variable to a date time as we want to approximate between dates.
2. Make a helper data frame using approx which interpolates between dates and adds the "zeros" or intersection points to the data. Setting the number of points n needs some trial and error to make sure that there are no overlaps visible to the eye. To my eye n=500 works fine.
3. Add separate variables for losses and profits and convert the numeric returned by approx back to a datetime using e.g. lubridate::as_datetime.
4. Plot the shaded areas via two separate geom_ribbons.

As you provided no sample data my example code makes use of some random fake data: