2984649 - Chart Legends not displaying items in sorted order in SAP Analytics Cloud (SAC)

Symptom

After applying a sort (Ascending/Descending, Highest to Lowest, Custom Sort) to a chart, the legend of the chart may not display the items in the same sorted order if there are nulls/gaps in the data.

Environment

SAP Analytics Cloud (Enterprise) 2020.21
SAP Analytics Cloud (Enterprise) 2022.17

Cause

The chart visualization itself should honor the sort order chosen, but the chart legend may not because the legend is generated based on processing the order of the groups/members in the chart. Ultimately, the chart isn't aware of the custom sort and can only honor what the query returns.

Let's illustrate what's happening with this example: TIME (Q1-Q4) is outer-most dimension (on the category axis) and PLACE (A, B, C) is innermost Color axis dimension with VALUE as the measure bound to the chart resulting in:

TIME	PLACE	VALUE
Q1	A, C	1, 2
Q2	B	3
Q3	A, B, C	4, 5, 6
Q4	B, C	7, 8

When the legend is built up, the processing begins with Q1, but only A, C can be added to the list of legend items since B doesn't have a value for Q1.
Once the processing reaches Q2, B is present, but A and C have no value. That’s ok since A and C have been accounted for in the legend already, but now B gets added to the legend which is now A, C, B.
After processing Q3 and Q4, we then end up with the legend as A, C, B.
The PLACE members are always in the right order within their respective in this example, however, since the sort/custom sort are determined by the back-end, we don’t know where to put the values we discover that have nulls in the result set since the back-end is responsible for sorting them in the first place.

Resolution

Possible workarounds:

Enabling the 'unbooked data' option from the ... menu for the corresponding dimensions in your chart in the designer panel.
Use a calculation to replace any null values with 0 so that a value is included in the resultset returned from the back-end. One example of a such a calculation (non-BW datasources) is:
```
IF (ISNULL (["ProfitAndLoss":Gross Margin]), 0 , ["ProfitAndLoss":Gross Margin] )
```

Note: In both cases above, the Top N (Auto) may be triggered, so you may need to manually override the Top N to a higher value, or filter the chart further to avoid it.

There are currently two known cases in which a workaround is not possible and will lead to unordered legend items:

The dimension on the Color shelf is a Calculated Dimension, which does not support the unbooked option
Ranking is applied that filters that affects which members of the dimension on the Color shelf that appear in the chart (including unbooked members), leading to the problem described in the cause

In wave 2022.17 a new feature has been added to help improve rank/sort on color dimensions. This feature is a new context menu option called "Ungroup Color Axis". Selecting this option will activate the new feature which should resolve the legend sorting issue mentioned in the symptom section. Selecting "Group Color Axis" will revert to the old behavior. The new option is found under the rank/sort options in the context menu.

NOTE: This feature cannot be toggled in the mobile app. Mobile view mode will use whatever has been defined when the story was last edited in the web version.

Feature Deactivated:

Using "Ungroup Color Axis" will change the query structure so that color dimensions are decoupled from the other dimension, which allows for the flexibility to sort color legends independently from chart data, and enables ranking on color dimension to look at the overall values associated with the color dimension and display the top N members.

Please refer to the table below for example comparison of old vs. new behaviors using an example model:

Scenario	Existing Behavior (Group Color Axis Selected)	New Behavior (Ungroup Color Axis selected)
Legend Sorting Issue
Top N on color dimension

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, sapanalyticscloud, sap analytical cloud, sap analytical cloud, SAC, sap analyst cloud, legend, sort, order , KBA , LOD-ANA-DES , Story Design & Visualizations , Problem

Product

SAP Analytics Cloud 1.0