The load order is also of no use if you plan to use the field in a rank expression.
In these case, one option is to create dual values for the field. A dual value contains a text representation and numeric value. Dates are numeric, containing the formatted date and numeric date representation. They display the formatted date, but sort using the numeric value. You can also perform arithmetic on the numeric value. But did you know that you can create your own, custom, dual values?
To do this, use the Dual() function. This is an example of a calculated field using Dual:
If(IsNull(PaymentAmount) Or PaymentAmount = 0, Dual('None', 0),
If(PaymentAmount > 0.9 * ExpectedInstallment Or IsExempted, Dual('>90%', 90),
If(PaymentAmount > 0.5 * ExpectedInstallment, Dual('50 - 90%', 50),
If(PaymentAmount > 0 Or InSuspense, Dual('0% - 50%', 1),
Dual('Reversal', -1))))) As PayGroup,
In this example, the load order does not reflect the required sort order, but values of Paygroup will sort according to the numeric value:
Reversal, None, 0% - 50%, 50% - 90%, >90%
Just remember to ensure that the “Numeric Value” sort option is selected when you need this sort order.