The ToNumber () function can resolve many issues related to data type incompatibility in Webi. Consider the Webi report below which consists of the “Calendar Period of Year” dimension.
You have noticed that these numbers are not sorted in descending or ascending order and even after applying a manual sort to the table the order will still be the same. The reason for this anomaly is that the data type is stored as “String” and not as “Number”. Zeroes are not recognized in the String data type. This means that the number “10” is recognized as “1” and thus the numbers are in incorrect order. To correct this issue the following steps need to be taken.
STEP 1] Create a new Dimension by going to “Data Access” > “New Variable”
STEP 2] Apply the ToNumber () Formula to the “Calendar Period of Year Number” dimension. I will name this new Dimension "* Calendar Period of Year Number”. As illustrated below, this new dimension is now recognized as a number:
STEP 3] Drag and drop the new dimension (i.e. “* Calendar Period of Year Number”) into the report.