Sort by Expression Changes When Selection: removing filters from sorting

[Sebastian_Dec]

Hi,

I have groupings starting with 10301, 10302 ... 10399 AND 103xx (I want 103xx to be at the end of sorting).
I figured out that I always check the first 5 numbers and the last two of them and whether it is equal to 'xx'.

Group are from 10000 to 99999 so there are enough to manually list everything (so there will be 100xx, 131xx, 555xx, 999xx etc.)

My sort by expression:

Aggr(
    If(Right(Trim(Left(Rodzina, 5)), 2) = 'xx', 9999999, Num(Left(Trim(Rodzina), 5))),
    Rodzina
)

The problem is that without the filter it's ok:

But with the filter for a year, the expression is changed:

I made a table showing the values ​​from the sort:

Without the filter:

Filter for 2024:

In 2024 there is no such data, but it appears in 2023, and since the 2023-2024 progression is calculated, these groupings will also appear.

So the final question: how to make my function resistant to the Year filter?

[nhenckel · Accepted solution]

You’re running into this issue because your Aggr() expression depends on the selection, causing the sort order to change with filters. The best way to resolve this is to move the sorting logic to the load script, which ensures consistent sorting and removes any runtime dependencies.

Solution: Add a Sort Field in the Load Script

In your load script, calculate the sort order like this:

LOAD
    Rodzina,
    If(Right(Trim(Left(Rodzina, 5)), 2) = 'xx', 9999999, Num(Left(Trim(Rodzina), 5))) AS SortOrder,
    *  // Load other fields as needed
FROM [YourDataSource];

Sorting in the Front End:

1. In your chart properties, go to the Sorting section.
2. Choose Sort by Expression and enter: SortOrder
3. Make sure sorting is set to Ascending.

This way, the sort order remains consistent regardless of selections (like Year filters), and the logic is precomputed for better performance.

[krishna20]

Can you try with this in Sort expression

Max({1}Your_Year_Column)

or 

Only({1}Your_Year_Column)