Get range of data from previous record

[ibobo]

Hi,  

I m trying to get data from a range of 20 previous record in a calculation field,  I used the fonction : GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -1)& ¶ & GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -2)& ¶ & .....& ¶ & GetNthRecord ( ffffffffield ; Get ( RecordNumber ) -20)

 

but in purpose to find the best result in my research I need to add or reduce the number of previous record, and there is 22 field with 20 previous record.  So imagine the work that I had to do to edit every field. I thought maybe it s possible to make a loop ? or something else that't I don't know ? 

I know this screen is wrong but maybe something like this ? 😛  I would like to keep it in calculation field so if I replace the 20 by 15  it will impact all calculated field and get result. 

 

thank you

[Steve Martino]

I would probably do this with a find, or a self join, and summary fields to gather the info.  But you're not saying what you want to do with this data, how you want to display it.

You could probably even do it with an Execute SQL statement, which would be even easier.

[comment]

I am also curious regarding the purpose of all this. When you say "previous record", it raises the question what exactly does "previous" mean: it could be the previous record in the current found set and sort order, or something more permanent (which could perhaps be better handled using a relationship?). And certainly having 22 fields (!) each looking back at 20 records is not going to be very efficient.

[ibobo]

the purpose is to analyse data of a currency. I have data of 22 currency of three month  , and I would like to analyse the relation between them. So there is few hundreds of period (few hundred of record of 22 field) Then I pick   20 previous period (record) of each period, then I take with a calculation field the highest and lowest value of each period, then I compare if there is relation between them. For exemple if three currency is the highest of 20 previous period what happens to others ? if something happen, the positive or negative conclusion would be the same if there is more or less period  selected ? 

for exemple 25/2/2019 15;00 (previous record would be 14:00, 13:00,..24/2/2019 11:00) 

I also remarked that when i use GetNthRecord  in a field calculation it keep same prevues records even through search fonction.  So when I run a search it keeps the prevues record from before search. That's a good thing  

I use this  for calculation of the lowest and highest  "Let(x=Substitute(Filter(CURRENCY20PREVIUES;"0123456789,¶");"¶";";"); Evaluate("Min(" & x &")"))"

Steve & Comment, thank you

[comment]

Wouldn't it be much simpler to find the 20 records you're interested in, then use summary fields to determine the minimum and maximum values?

I am not sure what comparisons you want to make between the different currencies; normally, you would have separate records for each currency, instead of 22 fields in the same record (which would then require 44 additional fields just to get the minimum and the maximum).

 

27 minutes ago, ibobo said:

I also remarked that when i use GetNthRecord  in a field calculation it keep same prevues records even through search fonction.  So when I run a search it keeps the prevues record from before search.

I don't think so. GetNthRecord follows the current found set and current sort order (when the referenced  field is from the same table). Unless your calculation is stored, so it will not update no matter what.

[ibobo]

can I use summary field and select only 20 records without using find mode ? or it will select only all records ? or I have to use find 20 records   to see the summary Max and Min ? 

[comment]

A summary field will summarize the entire found set, or - if placed in a sub summary part - the sorted group.

[ibobo]

I see, so each currency in a table, linked by same time, then use summery part to sort group of for exemple 24hours  for every hour  

 

thank you Steve and  Comment ( I always come with weird ideas but trust my it worth it 🤠  mostly ..)

[comment]

1 hour ago, ibobo said:

so each currency in a table

I think they can all be in the same table. But I still don't know what you're trying to accomplish here, so my ability to advise is limited.

 

 

Edited March 1, 2019 by comment