I am building a report that compares values in different rows of the table and have experimented with the PREVIOUS function. Unfortunately the table isn't sorted nicely, so PREVIOUS generally doesn't pick the correct value.
The data concerns a set of tier lines, so there will be T1, T2 etc. Each tier line may be updated in a number of amendments, so the expected result would be like this:
(my previous function has an additional check to avoid comparing across tiers)
However, because amendments to a tier is generally done after another tier is created, the tables rows are actually ordered like this:
and therefore the actual result I get when sorting by tier/amendment is:
How can I ensure that the PREVIOUS function see the table rows in the order I want?