SAP Knowledge Base Article - Public

3210173 - Sort incorrect, when sorting on an ID of a Dimension in a Table in SAP Analytics Cloud (SAC)

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

  1. In SAP Analytics Cloud, create a Table based on any Model.
  2. Add a Dimension to the Table Rows.
  3. Set the Dimension to display the ID only.
  4. Sort the Table on the Dimension showing the ID only, which shows numerical values only.
  5. 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:
    1. In SAP Analytics Cloud, open the Story.

    2. In Edit mode, select the Table.
        
    3. In the Builder pane on the right side, under "Rows", click on "Add Accounts/Dimensions"
        
    4. Then click on "Create Calculated Dimension"
        
    5. In the Calculation Editor, select the type: "Calculation Dimension"
         
    6. Enter a name for the Calculation Dimension.
        
    7. 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])
        
    8. Click "OK"
        
    9. 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.
          
    10. 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

Keywords

SAC , KBA , LOD-ANA-DES , Story Design & Visualizations , Problem

Product

SAP Analytics Cloud 1.0