Allows users to easily analyse offset sibling record field values, for example calculating the previous date of a transaction from the most recent or the previous product purchased before a significant purchase.
OFFSET_VALUE ( <link_table>, <offset_field>, <sort_field> , [<filter_query>] , direction/index )
-
link_table: The table on which to sort records by
-
offset_field: The field that contains the value to store by offset
-
sort_field: The field on which to sort records by
-
filter_query: A query string so that you can restrict the records being checked
-
direction/index: If to look back or forwards in the sibling records, entered as a negative value for backward and positive for forward.
Example: Create a field on the ORDERDETAIL table that flags what a Customer purchased prior to purchasing GPS, to determine if there is a trend.
OFFSET_VALUE ( Individual, [OrderDetail.Order Category] , [OrderDetail.Date of Order], [OrderDetail.Order Category] EQ "GPS" , -1 )