Values from previous record by date
Hi Ninox Experts
I am very often struggeling with the same simple problem and need your help. I concerns getting data from a previous record (I mean previous by date not by ID, in case the records are not entered chronologically) in a subtable
In this case I have two tables. Table "Fund" with only the field "Name" and in a subtable "Development". In Development I have 2 fields "Date" and "Value". Now I would like to calculate for each record in the subtable Development (for each "Fund" separately):
- most recent "date" before the current record date (in oder to calculate the difference in days sinc the last entry (by date not by ID) for this specific Fund
-"Value" for the previous record (by date not by ID) in order to calcualate the difference in "Value" between the current record and the last record (by date not ID) for this fund
It seems simple by I simply never get it right
Can you help. Thank you very much
Pascal
5 replies
-
PS: I tried the following for date but the formula field remains empty
let myRec := this;
let prevDate := max(Fund.Development[Date< this.Date].Date);
prevDateI would be very grateful for any help
-
You are very close. You just need to use the order statement.
If you don't use an order statement then Ninox sorts by record Id.
let myRec := this; let prevDate := max((Fund.Development[Date< this.Date] order Date)).Date; prevDate -
Thank you very much. I tried is, but strangely the formula field still remains empty.
The formula to me looks quite reasonable and I can not figure out what the mistake may be
-
Missed the second use of [this]:
let myRec := this; let prevDate := max((Fund.Development[Date< myrec.Date] order Date)).Date; prevDate -
Now it works. Thank you very much
Content aside
- Status Answered
- 3 wk agoLast active
- 5Replies
- 26Views
-
2
Following