Dual values for “self-sorting”

Sometimes you need a field that has a unusual sort order. For example, you might have an a range field that contains values <0, 0-10, 10-100, >100. When you use this field as a dimension in a table or chart, you will want to sort this in the order above. In many cases, this can be done by using the “Load Order” option, but there are cases where the natural load order may not work (calculated fields, for example).

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:

LOAD

...

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,

…

FROM …

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.