How to sort the selection filter at the top of a sheet?

[fishing_weights]

I am able to sort a field in multiple ways in the filter panel object. But once a value is selected and it pops up at the top of the application, it reverts to ascending order. Is there a way to change it to sort by descending order instead? 

[alexquimu]

Hey there,

Have you tried using a dual for that field? I created a sample calendar and I have also linked each year month its corresponding floor number for the monthend date. This way, the field is automatically ordered ascending. 

Let vStartDate = Floor(MakeDate(2024, 1, 1));
Let vEndDate = Floor(MakeDate(2025, 12, 31));

[TEMP_CALENDAR]:
LOAD
	$(vStartDate) + RecNo() -1						AS Date_Num,
    Date($(vStartDate) + RecNo() -1)				AS Date
AUTOGENERATE $(vEndDate) -$(vStartDate) +  1  
;

[MASTER_CALENDAR]:
LOAD 
	Date,
    Date_Num,
    Month(Date_Num) & ' ' & right(year(Date_Num),2)		
    												AS Month_Year_Text,
    Floor(MonthEnd(Date_Num))														
    												AS Month_Year_Num,
    dual(Month(Date_Num) & ' ' & right(year(Date_Num),2), Floor(MonthEnd(Date_Num)))		
    												AS Month_Year
                                                                                                       
RESIDENT [TEMP_CALENDAR];    

DROP TABLE [TEMP_CALENDAR];

In the front, I drew a filter pane, applied sort by state and numerically descending. Even when selections are made and values pop up at the beginning of the object, the values are still sortered descending.

Another approach would be to change the dual to multiply the number by -1, so the filter pane is automatically sorted descending. However, this would apply to all visualizations in the app.

dual(Month(Date_Num) & ' ' & right(year(Date),2), -1 * Floor(MonthEnd(Date_Num)))
AS Month_Year

Hope this helps.

Greetings,
Alex

[RafaelBarrios]

Hi @fishing_weights 

Sorry to say but you cant
its working as designed
https://community.qlik.com/t5/Official-Support-Articles/In-the-selection-bar-selection-list-sequence...

now, what you could do is to hide that panel but you will loose lots of functionalities and you will need to create you own buttons to apply or clear selection and many other features

in apps properties, click on UI settings

and turn off toolbar

hope this helps.
best,

[fishing_weights]

Hi @alexquimu thanks for the attempt but what you're sorting is just the filter pane and not the top filter drop down, which i guess by Rafael's reply is by design and no plans to change it anytime...

thanks anyway or if you have other out of the box ideas. happy to give it a go too

[alexquimu]

Hi @fishing_weights.

I am sorry, I did not realize you were talking about the top filter drop down. I assume you cannot assign the dual value to that field in the ETL to be automatically sorted by descending order, right? In that case, it works fine, though I imagine you can't do that because of the potential impact on your visualizations.

If that is the case, then you could hardcode the order of this field in the graphs, though I do not like this approach.

Greetings, 

Alex

[seanbruton]

Great solution... Happy Days

[fishing_weights]

Hi @alexquimu looks like it works based on your screenshot.

But can you help me understand what do you mean by using the dual in the ETL? Do you mean to order by the "dual" field?

Example

Table1:

field1,

field2,

Month_Year

Resident Table2

Order by Month_Year desc;

[alexquimu · Accepted solution]

Hi, @fishing_weights 

In order for my approach to work, you need to store the Month_Year field as a dual. I explain you the process:

You can create a field with dual capability directly in the load script (Dual - script and chart function | QlikView Help). By 'dual', I mean that a record can be registered as both a string and a number simultaneously. By default, filter panes, as well as active filters within the app, are sorted in ascending order using the numerical part of that dual field. You can create a dual field in the load script using the following syntax: dual(string, number). In this example, Month_Year is created as a dual field, where the string part represents each month and year, and the numerical part is the value corresponding to the end of the month

Let vStartDate = Floor(MakeDate(2024, 1, 1));
Let vEndDate = Floor(MakeDate(2025, 12, 31));

[TEMP_CALENDAR]:
LOAD
	$(vStartDate) + RecNo() -1						AS Date_Num,
    Date($(vStartDate) + RecNo() -1)				AS Date
AUTOGENERATE $(vEndDate) -$(vStartDate) +  1  
;

[MASTER_CALENDAR]:
LOAD DISTINCT
	Date,
    Date_Num,							
    dual(Month(Date_Num) & ' ' & right(year(Date_Num),2), -1 * Floor(MonthEnd(Date_Num)))		
    												AS Month_Year
                                                                                                       
RESIDENT [TEMP_CALENDAR];    

DROP TABLE [TEMP_CALENDAR];

This way, within the app, you can access both the string and the numerical parts as follows: by dragging the field, you get the text representation, and you can access the number by using the num() function

One of the main uses of the dual function is that the corresponding field is sorted automatically by its numerical value (by default, in ascending order). That is why I multiply the numerical part by -1 in the load script:

dual(Month(Date_Num) & ' ' & right(year(Date_Num),2), -1 * Floor(MonthEnd(Date_Num)))		
    												AS Month_Year

Therefore, by default, all fields will be sortered ascending by that field Month_Year:

Be careful: if you use the Month_Year field in any visualization, the dates will be sorted in reverse order compared to the usual chronological sequence (for example, from December 2025 to January 2025). If you want them to be sorted in the usual chronological order (from January 2025 to December 2025), you should modify the visualization: disable custom in the sorting options, then sort numerically descending.

Greetings,

Alex

[fishing_weights]

Thank you @alexquimu this works! Really Appreciate the detailed explanation too.

[alexquimu]

Hi, @fishing_weights 

Glad it worked for you!!

Greetings,

Alex