Help with formula

Thank you Sean, i'll give it a try.

Mcconneen, fields A and B are date fields, so i looking for the dates in A to be greater than the date in B, which i want to evaluate for each row. As time goes on, the B dates get beyond a relevant age, so want to exclude those rows where date A > date B.

Using spreadsheets in the past, i have no experience with "let... " expressions, so i will look forward to leaning about them in the next few weeks!

Ok, i've tried to implement this formula: it doesn't give any errors, and i did get results, but not the results i was after.

It's correctly summing C for the rows where A > B, but it's giving me a sum of all rows where A > B, whereas i'm after a cumulative total.

That is, if there are 10 rows where A > B their sum might be $5000. But what is the sum for only 3 rows? If i look at row 3, i'd like to see the sum of C (where A > B) to that 3rd row, not for all 10 rows.

What adjustments would get that result?

Hi Sean, thank you for your efforts, but i don't think this is the answer.

The reason being the cumulative total won't always increase as the record Id's increase.

To make things a little less abstract, i'm going to talk about the actual table and fields involved.  The table is called Tracking, and the fields are Expires (A), Filled (B) and Security (C).

At some point, a lot of the dates in Expires will become less than those in Filled, meaning the sums for Security will go down, possibly even to zero on occasion. Because Filled dates will become greater than many Expired dates, there will commonly be an ebb and flow in the Sums, going up, then coming down.

That's how i'd like it to work, anyway (and how it worked in the spreadhsheet.

Either i've translated the formula wrong, or it's not working right: it's giving me the same results as before.

If you would give the formula EXACTLY as i should input it, using the table name and field names i gave in my last post, i'll be able to see what's wrong compared to my version.

Maybe my translation of this was wrong, but i was getting the same results as before.

If you would write out the exact formula using the table name and field names i gave in my last post, any error i made might become obvious.

An, i thought my first comment above didn't post: i didn't realise it went to a second page!

I just tried it again, and it gave a slightly different, but still wonky result.

This was my version:

let t := this;

if Expires >= t.Filled then
sum((select Tracker where Filled = t.Filled and Expires > t.Filled and Expires <= t.Expires).Security)
end

What is "this"? Have i misinterpreted?

Ok, i found "this" in the manual. I should pick these things up after a while!

Yes, "wonky" was just an interim description to say i was still having problems.

This is the result of the formula:

The slight difference i mentioned was that one row was showing $1,700, which is the value (or sum) of that row, rather than all the rows.

But the other issue, which i didn't mention before, is that some rows are blank, rather than containing a cumulative total. So if row 5 has a cumulative total of $5000, and the  forumla doesn't include row 6 in its summing, i'd still want row 6 to show the cumalative total to that point ie $5000.

Have a good trip!

The language reference in the manual is very informative.

I'll try to go through it in the coming days...

The screenshot that follows is an extract of the spreadsheet. I have a frozen header row, so the rows that follow are in mid-stream: that is, they reflect transactions that have gone before.

There are a couple of things about the spreadsheet that i've taken a different approach to in the database, but i don't think it's still valid an an illustration of what i'm after.

I meant to say it's still valid as an illustration of what i'm after.

Security is a fomula. RST is a formula: the one i'm asking for help with. Filled and Expired are date fields.

The reason some date fields are blank is because some orders don't get filled.

One minor 'error' with the spreadsheet at that stage was that some orders have no Expires dates even though they have Filled dates. This was due to completing the Filled date prematurely on the expectation the orders would be filled. However they weren't, hence no Expires date. At that point, they weren't 'cleaned up' by deleting the false Fill dates.

That said, i understand your frustration: i wouldn't have a hope in hell of working out this formula given my current state of knowledge. You likely have other things you could do, so if you want to drop it, i appreciate your efforts so far, no hard feelings.

Oh, there's also one record with an Expires date but no filled date. The order concerned didn't get Filled, so i'm not sure why there's an Expires date, but there was a computer crash at the time, so that may have something to do with it.

Again, had the spreadsheet been 'cleaned' properly, the Expires date for that record would have been deleted.

Also since the records are from a demo sheet, the requirements to keep it clean weren't as important.