90 – Dual Data Type Caveat

Dual combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes.

Now when I think of the data type combining both text and a number value, I tend to think that this means a value can be the unique combination of the two. However, this is not the case.

The number in Dual is the core value, while the text is just the display value.

This means a number value can only have one display value. While a text value can be the display value of multiple numbers.

Output:

I have made this mistake when trying to combine mixed granularity within dates. Say at the end of the year aclient’s finance team closes the books and closes their general ledger at the end of the year. They then report his at a year level. However, in the current year, they report at the month level.

To incorporate both sets of data in a chart I came up with the concept of doing Month-Year, with PY being a whole previous year.

Then a new requirement came and a particular budget was only at the year level. So we stuck with a similar concept, but this time CY.

Now this worked out as is. However, I had made the Month field a Dual. It took me far too long thatDual(‘PY,0) and Dual(‘CY’,0) would not provide the expected results. It would always default to PY since there can only be one display value per number.Hopefully knowing this can save you some time in the future.