Calculated field expression

[bubbleT]

Currently I have the "Organisation" and "Submission Date" columns. I want to create a 3rd column "Order", which indicates how recent an entry was submitted by each Organisation , with 1 being the most recent. The "Order" column would then be able to help me create a filter view for the latest record by Organisation, as well as for comparison between past records.

I understand from the calculated field docs that "The expression cannot result in any aggregation of data from several records, or use inter-record functions to refer to data in other records."

Given my requirements (create a filter view for the latest records, as well as for comparison between past records), would adding this column be possible, or are there better/alternative approaches to meet my requirements?

Thanks in advance!

[Digvijay_Singh]

I don't understand how did you create values for 3rd column, can you pl elaborate?

[bubbleT]

It indicates how recent an entry was submitted by each Organisation , with 1 being the most recent. Sorry for the confusion, edited original post as well.

[Ahidhar]

try this

tab:
load *,window(recno(),[Organisation Name],'Desc',[Submission Date]) as Order;
load [Organisation Name],date#([Submission Date],'DD/MM/YYYY') as [Submission Date] Inline
[
Organisation Name,Submission Date
A,5/8/2021
A,8/9/2023
A,22/8/2022
B,11/9/2020
B,8/3/2023
B,10/12/2021
];

[Rohan]

Hi,

You can certainly bring this column from the back end, or you can try to use rank function in the front end. Like :
Rank(Total<[Organisation Name]>  num(Submission_Date))

Try this & let me know if it worked for you.

Regards,

Rohan.