0

Help with formula

I'm moving from a spreadsheet to Ninox, so have to translate a lot of the formulas into Ninox.

So far, with some help from Joerg, i've been able to tackle quite a few, but i've come to a formula i'm not really sure how to move over.

What i want to do could be described in the following way: if any of Field A is >= the most recent value of Field B,  then sum the values of Field C in those rows (where Field A is >= Field B).

All these fields (columns) are in the same table.

I've come up with an initial version of this, but i doubt it's right. Sticking with the A, B, C field names:

if A >= max(B) then sum(C) end

27 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    Try this...

     

    let tB := this.B;
    sum((select YourTable where B = tB and A > tB).C)

    • Mconneen
    • 4 yrs ago
    • Reported - view

    What drives "the most recent value of Field B" ... Is it the last modified?   Is it the last created?   Or .. in Sean's example above.. is it the row that you are currently viewing? 

    • Rico
    • 4 yrs ago
    • Reported - view

    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!

    • Rico
    • 4 yrs ago
    • Reported - view

    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?

    • Sean
    • 4 yrs ago
    • Reported - view

    This solution assumes the cumulative total will increase as the record Id's increase. If that doesn't work we'll have to try something else. This also will leave the value blank if A is not greater than B.

     

    let t := this;
    if A > t.B then
    sum((select YourTable where B = t.B and A > t.B and Id <= t.Id).C)
    end

    • Sean
    • 4 yrs ago
    • Reported - view

    I just found an error with that solution... I'll have to work on this tomorrow.

    • Sean
    • 4 yrs ago
    • Reported - view

    I figured out it works, but you have to either click the Refresh icon at the upper left corner of the table or add code to the "Trigger after update". You could put this code in one of the field triggers A? B? and it will refresh the table when you add a new record or make changes to that field.

     

    let tId := this.Id;
    openRecord(record(YourTable,tId))

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Sean
    • 4 yrs ago
    • Reported - view

    Hi Rico, you're welcome. The formula should still work with a minor change.

     

    let t := this;
    if A > t.B then
    sum((select YourTable where B = t.B and A > t.B and A <= t.A).C)
    end

     

    I did test it and it does give a cumulative result for C. Let me know if you get it to work or not.

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Rico
    • 4 yrs ago
    • Reported - view

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

    • Rico
    • 4 yrs ago
    • Reported - view

    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?

    • Sean
    • 4 yrs ago
    • Reported - view

    "this" is a handle to the current record. I'm afraid "wonky" isn't a very helpful description of what you are getting relative to what you want to get. Maybe a screenshot of your spreadsheet if the data isn't sensitive. There has to be a rule for accumulating the amount and in this case I used the value of "Expires" which makes sense to me based on the description of what you are trying to accomplish.

     

    I'm driving from Santa Rosa, NM to Temple, TX today so I will be out of pocket for the duration.

    • Rico
    • 4 yrs ago
    • Reported - view

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

    • Rico
    • 4 yrs ago
    • Reported - view

    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!

    • Rico
    • 4 yrs ago
    • Reported - view

    The language reference in the manual is very informative.

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

    • Sean
    • 4 yrs ago
    • Reported - view

    Can you make a screenshot of your spreadsheet that includes Filled, Expires and Security. It doesn't have to be your live data, I just want to see a working example of what you have in your spreadsheet.

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Rico
    • 4 yrs ago
    • Reported - view

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

    • Sean
    • 4 yrs ago
    • Reported - view

    Well, I have to admit I'm getting a little frustrated with this one. I think the latest screenshot should have been presented in the beginning with an explanation for which columns are data, which are calculations, why are there blanks in the date columns. If you want to explain EXACTLY how all that works I'm willing to try to help, otherwise, I think I'm going to drop out.

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Rico
    • 4 yrs ago
    • Reported - view

    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.

    • Sean
    • 4 yrs ago
    • Reported - view

    This is my crossword puzzle so I don't mind doing it. A long delay a shipper really messed up my schedule so it will be much later today, CDT, before I can look at it.

    • Sean
    • 4 yrs ago
    • Reported - view

    I think you are going to need, or at the very least, it will be easier if you add a Transaction Id field because you have multiple records where Filled has the same date. You can automatically fill the Transaction Id field of existing records using a Button object or use the Console. After that, you can set up a Trigger that automatically generates the next Transaction Id when you add a record.

     

    Some others might have a different opinion, but that's my suggestion.

Content aside

  • 4 yrs agoLast active
  • 27Replies
  • 5090Views