Symptom
- Incorrect sort.
- ID, are not sorted in numerical order.
- When sorting on an ID of a Dimension in a Table in SAP Analytics Cloud, the values are not sorted in numerical order.
Environment
- SAP Analytics Cloud
Reproducing the Issue
- In SAP Analytics Cloud, create a Table based on any Model.
- Add a Dimension to the Table Rows.
- Set the Dimension to display the ID only.
- Sort the Table on the Dimension showing the ID only, which shows numerical values only.
- Notice the rows of the Table are not sorted in numerical order, but in a string order.
How to sort the ID in numerical order in a Table?
Cause
- ID of a Dimension are alphanumeric values.
- When sorting on the ID, it will therefore perform a string sort, and this is why the values are not sorted in the expected numerical order.
Resolution
- To sort an ID in numerical order on a Table, create a Calculated Dimension that converts the ID of the Dimension to a number, using the function TONUMBER, then in the Table, sort on the Calculated Dimension:
-
In SAP Analytics Cloud, open the Story.
- In Edit mode, select the Table.
- In the Builder pane on the right side, under "Rows", click on "Add Accounts/Dimensions"
- Then click on "Create Calculated Dimension"
- In the Calculation Editor, select the type: "Calculation Dimension"
- Enter a name for the Calculation Dimension.
- In the Edit Formula window, convert the alphanumeric values of the Dimension ID to a number using the function: TONUMBER. Below is an example of the formula:
ToNumber([<MODEL NAME>:<DIMENSION NAME>].[ID])
- Click "OK"
- Still in the Builder pane, under Rows, remove the original Dimension, since you will now be using the Calculated Dimension to display the ID of the Dimension.
- In the Table, right click on the Calculated Dimension created, and select "Sort Options", and select the desired sort direction: Ascending, or Decending.
See Also
- 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
- Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
- 2487011 - What information do I need to provide when opening an case for SAP Analytics Cloud?
- 2511489 - Troubleshooting performance issues in SAP Analytics Cloud
- SAP Analytics Cloud Connection Guide
- Getting Started with SAP Analytics Cloud Expert Community page
- SAP Analytics Cloud Get More Help and SAP Support
Keywords
SAC , KBA , LOD-ANA-DES , Story Design & Visualizations , Problem
Product
SAP Analytics Cloud 1.0