Qlik Sense Analytics: Unable to sort measures when using chart function

[Nao_Baba]

In Qlik Sense Enterprise Analytics, using the Above() function in a measure can lead to unexpected behavior when sorting; when a measure includes the Above() function, sorting (including other measures on the same chart or table) is not supported. 

Example: 

In the following Straight Table, the third column (Running Total) uses an expression in its measure to calculate a running total by adding the previous row's value.

rangesum(above(Sum(SalesAmount),0,RowNo()))

In this case, you cannot change the sorting order to ascending or descending for the second column, "Sum(sales)", or the third column, "Running Total", since the Above() function is used in the third column.

This is working as designed:

Sorting on y-values in charts or sorting by expression columns in tables is not allowed when the chart function (Above, Below, Bottom, Column, Dimensionality) is used in any of the chart's expressions. These sort alternatives are therefore automatically disabled. When you use this chart function in a visualization or table, the sorting of the visualization will revert back to the sorted input to this function.

Source: Above - chart function | Limitations 

Resolution

As a workaround, you can use a structured (sort) parameter in the Aggr() function.

For example, you change the expression like the following:

sum(Aggr(Rangesum(Above(Sum(SalesAmount),0,rowno())),(SalesAmount, (Numeric, Ascending)) ))

Now you can change the sort to ascending or descending order in measure columns.

However, it does not recalculate based on the new sorting order due to a limitation of the Aggr() function, which returns results based on the calculated hypercube. The expression (Numeric, Ascending) part will have to be modified in order to reflect another sort order if necessary.

Internal Investigation ID(s)

SUPPORT-9243

Environment

• Qlik Sense Enterprise on Windows
• Qlik Cloud